Including information from multiple tables

[rich]

Apologies if the answer to this is somewhere on the forum... I have no idea what to use in my search criteria for this one!



I'm trying to create a query that takes information from UPTO three tables. The problem is, that its only including results where records exist in all three tables and I need it to include records even if they only have entries in two of the tables.

The tables are linked by a common piece of information - a membership number. The problem is, that all members don't appear in all three tables. They do always appear in two of the tables, and that remains consistent (ie, they're all in the same two tables), but the third table contains optional information, so there isn't always an entry in the third table for every record in the first two tables.

I can't figure out how to get it to list all records from table 1 (and table 2), regardless of whether an entry exists for it in table 3.

[rich]

Okay, so maybe its something about joins...

This is an extract from the SQL for the query as it is now -

FROM tbl_results INNER JOIN (tbl_applicant LEFT JOIN tbl_audition ON tbl_applicant.ID = tbl_audition.ID) ON tbl_results.ID = tbl_applicant.ID

So you can see the three relevant tables

tbl_audition - Always a unique record
tbl_applicant - maybe used several times - applicants may have one or more audition records.
tbl_results - May or may not exist for any applicant record. Each applicant will have zero or one results record.

[rich]

Ah, fixed it by changing the FROM line to

FROM tbl_audition INNER JOIN (tbl_applicant LEFT JOIN tbl_results ON tbl_applicant.ID = tbl_results.ID) ON tbl_audition.ID = tbl_applicant.ID