3 table join returning too many values

[wrbodine]

Hi,

I have three tables that all have the same ID value in them. I have a query that where I want to bring back data joined on two tables, and include a value from a third table when it is in the 2nd table (otherwise null for that field). So basically I want to do an inner join on tbl1 and tbl2, and do a left join on that result set and tbl3. How would I code this?

I tried the following:

Select distinct tbl1.ID, tbl2.otherField, tbl3.anotherField
from tbl1 join tbl2
on tbl1.ID = tbl2.ID
left join tbl3
on tbl2.ID = tbl3.ID

but its returning too many rows. Maybe there's some things I don't understand about joins because when I did a left join for just 2 tables, it didn't return the same number of rows as the left-side table. Any ideas?

 

[tlbroadbent]

The query you've created will do the following. Select records from tbl1 and tbl2 where they match on ID. All of those records will be returned and the correspoding column from tbl3 if the same ID exists on tbl3.

ID Other Another
1 ABCD XYZ /* tbl1, tbl2, tbl3 contain ID=1 */
2 ACEF NULL /* tbl1, tbl2 contain ID=2 */
3 ASER QWER /* tbl1, tbl2, tbl3 contain ID=3 */
4 NHYT NULL /* tbl1, tbl2 contain ID=4 */

Is this the result you expect? What you expect is not clear from the description. Also, do you have any criteria on the query that you haven't posted?

When you do a left join, the query should return at least as many records as on the left side unless some criteria is applied that reduces the number. The query can return more records than the on the left side of the join if multiple records on the right side match each left side record. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[wrbodine]

Hey Terry,

That query that you pictured is totally the result that I'm looking for; matches from tbl1 and tbl2, and a row for the 3rd table with a value when its there and NULL when its not. I think your last paragraph may describe the problem I'm having; maybe the 3rd table has multiple records that match and it is returning extra rows? I.E., when I run the query with just the first 2 tables and an inner join, it gets the right number of rows, and I don't want the adding of a field from the 3rd table to change that number.

Thanks,
Ray

P.S. - there's no other criteria on the query; a few more fields but the example I gave is as complex as it gets....

 

[tlbroadbent]

If you have Multiple records on tbl3 with the same ID and you only want one of them, you will need to do something to limit the number. Obviously the column selected from tbl3, AnotherColumn in your example, has multiple values or the disntinct clause would eliminate duplicates. Now you need to decide how to handle the duplicated record.

Do you want the maximum or minimum value of Another Field? Do you want to choose the value of AnotherField based on a different criteria such as a date? Or is the query in fact returning the correct result and your assumptions about the data need to change?

You could eliminate duplicates with aggregate functions and a group by clause. The following example uses the MAX function in a subquery.

Select
tbl1.ID, tbl2.otherField,
q.AnotherField
from (tbl1 join tbl2
on tbl1.ID = tbl2.ID)
left join
(Select ID, Max(AnotherField) As AnotherField
From tbl3 Group By ID) As q
on tbl2.ID = q.ID
Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[wrbodine]

Thanks Terry,

This is really helpful. It turned out there were multiple values in the third table (and there weren't supposed to be) and this is helping us resolve the issue.