Multiple Outer Joins in Access

[evoluder]

Hello,

I am trying to build a VB ADO recordset off of several outer joins in an Access DB, the syntax for the SQL I am using is:

SELECT table1.field2, table2.field2, table3.field2
FROM table1 LEFT OUTER JOIN table2
ON table1.field1 = table2.field1
LEFT OUTER JOIN table3
ON table1.field1 = table3.field1
WHERE (table1.field3 = 1)

I can get the SELECT to work with one outer join, but as soon as I add another one I get a syntax error. Table1 is populated with fields that I want to grab, table2 and table3 both have a relationship to table1.field1. Table1 always has data but only table2 or table3 will have data for the corresponding key, field1.

I've tried several different variations with no luck...anyone out there know my mistake with the syntax? I am new to SQL and VB so I won't be insulted if you tell me I am doing something silly.

Thanks for your help.

 

[jhall156]

I think you need to use some parens in Access like:

SELECT table1.field2, table2.field2, table3.field2
FROM (table1 LEFT OUTER JOIN table2
ON table1.field1 = table2.field1)
LEFT OUTER JOIN table3
ON table1.field1 = table3.field1
WHERE (table1.field3 = 1)

That should do the trick. Finicky friggin' Access.

 

[zerosandones]

The problem is that you have not delineated the first outer-join expression from the second one. Adding parentheses as follows will correct the problem:

SELECT TableOne.Field2, TableTwo.Field2, TableThree.Field2
FROM (TableOne LEFT JOIN TableThree ON TableOne.Field1 = TableThree.Field1) LEFT JOIN TableTwo ON TableOne.Field1 = TableTwo.Field1
WHERE TableOne.Field3 = 1;

 

[jhall156]

That's what I said. It really doesn't make any sense to me. There is no precedence between the joins. The only thing resembling such a thing is the ON clause for each join. Go figure...

 

[evoluder]

It works as you both describe. Why can't Access do it like everybody else? Thanks for the help!