multiple table relationships

[tasander]

Hi

I have 2 tables as per below, table 1 has the exact people i require in it but table 2 has an extra bit of detail i need to pull in. Currently i have created 2 relationship from table 1 to table 2 (firstName - ChristianName) and (Surname - SecondName). these are on a include all records from table 1 and equal records from table 2 join type.

Table1
FirstName Surname

Table2
ChristianName SecondName PhoneNumber


When i run a query to pull back the data i am getting more records than are in table 1, after some investigation it was duplictaing rows in table 1 where it found a match in table 2 but there was a null PhoneNumber.

Can anyone help me with this?

 

[Joe4]

If you end up with more records in your query than exists in table 1, that means you have a one-to-many relationship between the two tables instead of a one-to-one match. So, for some of your records in table 1, there are multiple records in table 2 that match it.

So, you need to determine what you want to do in those cases. If a record in table 1 matches two records in table 2, which matching record do you want to return?
Once you figure out the logic you want to use, you can try to incorporate that into the Criteria of your query.

 

[tasander]

Perfect, thanks