CODEWITHSUNDEEP
sqlms-access
QtheNovice
QtheNovice

Reputation: 95

Need query to return Null fields if no associated data in joined table

I'm trying to design a query in Access 2003 that returns information for a directory. One of the values I wish to include is contained in a second table, but that table doesn't have records for all the records I want.

I want a directory line for EACH MomID in table Registration, regardless of whether that MomID is referenced in table Kids. I can't just leave it out, because if there are children listed in Kids, that needs to be in the directory.

Currently, my query returns only those records where MomID is in both Registration and Kids. I'm filtering the data so that I only get records from Registration with the current YearStart field, so I can't make it an OR statement (even if I knew how). How do I get EVERY record in Registration which meets the YearStart criterion, and have the query return an empty field if the relevant MomID isn't present in Kids"

I could just go manually add MomID to Kids and leave the other fields blank, but that solution lacks elegance. I'm also not entirely sure that would work, since I'm running the data in Kids through two other queries to sort and concatenate before pulling it into the directory query. Plus, I'd like to retain the ability to include childless individuals in the directory if I ever want to expand the database to include the volunteers associated with the group.

Upvotes: 2

Views: 2399

Answers (1)

suff trek
suff trek

Reputation: 39777

What you're describing is a classic case of LEFT JOIN. This operator brings ALL records from the left part of the join even if there're no matches in the right part,

In your case it would be something like

SELECT * FROM Registration LEFT JOIN Kids ON Registration.MomID = Kids.MomID
WHERE Registration.YearStart = Year(Date())

Substitute * with only needed fields if you don't need all the fields from both tables.

Upvotes: 3

Related Questions