acc 2007: help with multiple table outer join syntax

[vl]

Hi All,

I've been searching the groups for help on my outer join syntax. So
far, I get the hints that I should be using parenthesis. It helped
for 3 tables. But what if I want to join more - say 4 to 8? tables?

Here's my SQL:
SELECT
findings.finding_id,
findings.finding_date,
findings.discussion,
vet.last_name,
clinic.clinic_name,
patient.patient_name
FROM
( findings LEFT JOIN vet ON (findings.vet_id = vet.vet_id) )
LEFT JOIN clinic ON (findings.clinic_id = clinic.clinic_id)
LEFT JOIN patient ON (findings.patient_id = patient.patient_id)

Error message:
Syntax error (missing operator) in query expression
'(findings.clinic_id = clinic.clinic_id)
LEFT JOIN patient ON (findings.patient_id = patient.patient_id'.

If I take out the last LEFT JOIN clause (patient table), and remove
the patient_name column, the query runs fine.

I also played with the placement of the parenthesis... to no avail.

What am I doing wrong?

TIA,
-vl

 

[Bob Barrows [MVP]]

Hi All,

I've been searching the groups for help on my outer join syntax. So
far, I get the hints that I should be using parenthesis. It helped
for 3 tables. But what if I want to join more - say 4 to 8? tables?

Here's my SQL:
SELECT
findings.finding_id,
findings.finding_date,
findings.discussion,
vet.last_name,
clinic.clinic_name,
patient.patient_name
FROM
( findings LEFT JOIN vet ON (findings.vet_id = vet.vet_id) )
LEFT JOIN clinic ON (findings.clinic_id = clinic.clinic_id)
LEFT JOIN patient ON (findings.patient_id = patient.patient_id)

Error message:
Syntax error (missing operator) in query expression
'(findings.clinic_id = clinic.clinic_id)
LEFT JOIN patient ON (findings.patient_id = patient.patient_id'.

If I take out the last LEFT JOIN clause (patient table), and remove
the patient_name column, the query runs fine.

I also played with the placement of the parenthesis... to no avail.

What am I doing wrong?

Failing to use the Access Query Builder. Open your database in Access, go
to the Queries tab, click the button to create a new query in design view,
add your tables, draw the links, then switch to SQL View to see what the
correct syntax for the joins is supposed to look like.

Proper nesting via parentheses is the key. I have never been able to get it
right in 15 years without using the query builder, especially now that I
have been using SQL Server for the past 8 years: Transact-SQL does not need
joins nested by parentheses, so I haven't had to think about it for a while.

I think it's like this:
FROM
((((a join b on ...) join c on ...) join d on ... ) join e on ... )
join f on ...

The idea is that join expressions (a join b on ... ) need to be nested if
there are more than two tables being joined. If there are only two tables,
no nesting is required. The idea is that only two operands can be used with
the join operator. Parentheses are used to make a join expression look like
a single operand.

But don't hold me to this. Use the query builder.

 

[John Spencer]

Keep adding parentheses. Here is one way.

(((( findings LEFT JOIN vet ON findings.vet_id = vet.vet_id )
LEFT JOIN clinic ON findings.clinic_id = clinic.clinic_id)
LEFT JOIN patient ON findings.patient_id = patient.patient_id)
LEFT JOIN xxx ON ...)
LEFT JOIN yyy ON ...

If you want to study it, the easiest way is to use the design view (query
grid) and build queries there and then switch to SQL view to see how Access
places the parentheses.

John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County