SQL Join Multiple Tables

[JB Private]

I need some help trying to figure out how to retrieve data from 3 different tables, when there are records in 1 table that don't exists in the other tables.
Here's the situation
Table1
Fields: RefNum & PartNum
In this table there may be multiple PartNum associated with a RefNum
Ex:
RefNum | PartNum
4-1 | ABC
4-1 | DEF
4-2 | AAA
4-3 | BBB

Table2
Fields PartNum, NewPartNum
The PartNum will match up to Table1.PartNum although there may not be a match at all
Ex:
PartNum | NewPartNum
ABC | 123
AAA | 999
BBB | 888

Table3
Fields PartNum, Price
This PartNum will also match up to Table2.NewPartNum
Ex:
PartNum | Price
123 | 100.00
999 | 200.00
888 | 300.00

What I am trying to do is find all records that belong to Table1.RefNum. Then using those Table1.PartNum to find the NewPartNum in Table2 and the Price from Table3

The problem I'm having is that if there is not match from Table1.PartNum = Table2.PartNum, then it returns no data. For example, if I restrict the query to Table1.RefNum="4-1", what I would like to get returned is:

Table1.RefNum | Table1.PartNum | Table2.NewPartNum | Table3.Price
4-1 | ABC | 123 | 100.00
4-1 | DEF | Null | Null


Unfortunately I can't figure out the correct way to join these 3 tables so that I see ALL the results from Table1 and only the matching results from Table2 & Table3 (with "nulls" listed for those that don't have a match)

[ck9663]

Use a LEFT JOIN to table2 and table3.

Caution: Check for duplicate.

Happy Coding!!!

~~ CK

[JB Private]

Unfortunately I'm still getting just the one result. The stmt I'm using is:

Select Table1.RefNum, Table1.PartNum, Table2.NewPartNum, Table3.Price from (Table1 LEFT OUTER JOIN Table2 on Table1.PartNum=Table2.PartNum) LEFT JOIN Table3 on Table3.PartNum = Table2.NewPartNum
Where Table1.RefNum='4-1'

The result I get is
4-1 | ABC | 123 | 100.00

The result I want to get is
4-1 | ABC | 123 | 100.00
4-1 | DEF | Null | Null <----to show there is no partnum "DEF" in either Table2 or Table3


Any ideas what's wrong w/ the select stmt?

If I remove the 3rd table from the mix and just join Table1 & Table2, I get the result I'm after (listing both results from Table1 and "null" for the Table2 item that doesn't have a match). But once I throw the 3rd table back into the mix, I only get the 1 result from Table1 instead of both items (w/ nulls for non-matching one)

[ck9663]

Try removing the parenthesis on your join, the one after "from".


Happy Coding!!!

~~ CK

[JB Private]

well found the problem. In my example, this was just a short version of a much longer query and the problem was in the longer query part of the "where" clause was to restrict the results of only US$ prices from the price table. But by doing that, if the result included a "null", then of course those records weren't returned either. So by simply adding a "where Table3.currency='USD' or Table3.currency is null" then the query returned all records, including ones without a match.

I realize if I had provided ALL the info your response would have probably saved me a lot of time. But by confirming the whole "left join" stmt I was at least able to narrow down my problem & solution to something other than the way I was joining the table.

Thank you!!