Query help - need to return columns even without matches

[GATVA]

I need help with a query in Access where I am trying to return all records in one table whether or not there is a match to all of the tables that I am selecting from. I'll do my best to explain this.

For example, I have table A, B, C, D.

I need to return a row for every record in A. I want to return data from B if there is a match, otherwise null. I want to return data from C if there is a match, otherwise null. I want to return data from D if there is a match, otherwise null.

So if A is related to B,C,D by zip code, I want to return the data in B,C, or D if there is a matching zip code in the table. If not I want the cloumn to return a null value. So If I only find a match in C, it would be like this:

Zip B value C value D value
24502 24502

I hope this makes sense. Thanks in advance.

 

[lespaul]

what you need is an outer join (check out Understanding SQL Joins)

SELECT * FROM TableA a
LEFT OUTER JOIN TableB b on a.Zipcode = b.zipcode
LEFT OUTER JOIN TableC c on a.Zipcode = c.zipcode
LEFT OUTER JOIN TableD d on a.Zipcode = d.zipcode

Leslie

Have you met Hardy Heron?