Multiple outer joins in access

[Patrick Ryan]

Can I have multiple outer (left) joins, joining 3 or more tables, in an
Access SQL statement? If so, what is the syntax? A left join between 2
tables is not a problem.

I have a table with foreign keys to 4 other tables as well as its own data
fields. The fields that hold the foreign keys may have valid long integer
values (keys) or nulls. Therefore, I can't use inner joins. I want the SQL
statement to return the data fields from the "join" table as well as the
related values from the 4 tables via the foreign keys (if they exist).

I can actually do this by making 5 separate SQL statement connected by a
union statement. But I think this would take a big performance hit with a
lot of data. I am currently handling the above problem with lookups on the
application level. I'd prefer not though. Any ideas? Thanks.

 

[Nikos Yannacopoulos]

Patrick,

Set up your query graphically in a plain query design view, then change to
SQL view and it's all there waiting for you!

HTH,
Nikos

 

[John Spencer (MVP)]

Yes and No.

It depends on which tables are being joined to which tables.

Select <FieldList>
FROM (TableA LEFT JOIN TableB
On TableA.PK = TableB.FK)
LEFT JOIN TableC ON
TableA.PK = TableC.PK

I think that will work. It is a matter of which fields you use in the join and
how you place the parentheses. In the query grid, you would be joining from
TableA to TableB and From TableA to TableC.