[Resolved] JOIN: need data from other tables (if any)

[sneakyimp]

I have a master table called Hd2003. I need to retreive certain fields for EVERY record in that table and i need to fetch certain fields from 3 other tables IF any corresponding record exists. the 4 tables i'm dealing with are connected by the field unitid.

I'm confused about my JOIN query. what i need to be sure of is that if Ic2003 has no corresponding record, then i still get values for Ic2003_ay and Sfa0203 if there are any records there.

Will this query work? Or will it drop any SF records when there is no IC record?

SELECT hd.instnm, ic.actcm25, ic_ay.chg1ay3, sf.anyaidn
FROM Hd2003 hd
LEFT JOIN Ic2003 ic ON hd.unitid = ic.unitid
LEFT JOIN Ic2003_ay ic_ay ON ic.unitid = ic_ay.unitid
LEFT JOIN Sfa0203 sf ON ic_ay.unitid = sf.unitid

PS: this is a beast of a query on my machine...there are about 7000 records in each of these tables.

[tomhath]

It won't work the way you have it. Use the unitid from the primary table in all of the joins and it should do what you want:

SELECT hd.instnm, ic.actcm25, ic_ay.chg1ay3, sf.anyaidn
FROM Hd2003 hd
LEFT JOIN Ic2003 ic 
ON   hd.unitid = ic.unitid
LEFT JOIN Ic2003_ay ic_ay 
ON   hd.unitid = ic_ay.unitid
LEFT JOIN Sfa0203 sf 
ON   hd.unitid = sf.unitid 

This should be fast if you have the unitid column indexed on all four tables; 7000 rows per table is not very many to a decent DBMS.

[sneakyimp]

THANK YOU, Tom!

You are awesome. Added indexes and now query runs with the exact number of results in less than 3 hundredths of a second. cheers.