LEFT JOIN question 1

[GerryGoldberg]

Using Access97, I am trying to create a query to find rows in one table (table1) that don't have any matching rows in a second table (table2). Both tables have compound primary keys of field1 and field2. My query looks like this:

SELECT field1,field2
FROM table1 LEFT JOIN table2
ON table1.field1=table2.field1 AND
ON table1.field2=table2.field2
WHERE table2.field1 IS NULL and table2.field2 IS NULL

I keep getting an error message that says "Syntax error (missing operator) in query expression". What am I doing wrong?

Thanks,

Gerry Goldberg

 

[tlbroadbent]

Remove the second 'ON'. The correct syntax is

SELECT field1,field2
FROM table1 LEFT JOIN table2
ON table1.field1=table2.field1
AND table1.field2=table2.field2
WHERE table2.field1 IS NULL and table2.field2 IS NULL

Terry

http://members.home.net/tlbroadbent/prog.htm

The reason why worry kills more people than work is that more people worry than work. - Robert Frost

 

[GerryGoldberg]

Apparently, the syntax shown in the Help file for Access 97 is incorrect. Your suggestion solved my problem.

Thanks!

Gerry Goldberg