WHERE Clause on LEFT JOIN : why do I get Join expression not supported message?

[AndrewB]

I've been toiling with the issue of WHERE clauses on the "Right" side of Left Joins. I'm aware that you need to use JOIN ON......AND.... rather than JOIN ON....WHERE.... if the WHERE relates to the Right Hand table.

I've even got an example in my DB where the above works, but now am struggling to use the same theory for other tables. Therefore, I went and created two Mickey Mouse tables to test the logic but am getting an error.

I have
Table 1 with one field called Field 1 - values A, B, C
Table 2 as follows

Field 1.....Field 2.......Field 3
A.............100
C.............200..........XXX

I hoped to have a query that finds all records on Table 1 and records on Table 2 where Field 1 matches on the two tables and Field3 = XXX

My SQL is
SELECT Table1.Field1, Table2.Field1, Table2.Field2, Table2.Field3
FROM Table1 INNER JOIN Table2 ON Table1.Field1 = Table2.Field1
AND Table2.Field3="XXX";

but I get Join Expression not supported

What am I doing wrong?

Thanks
Andrew

 

[MStef]

Hello Andew!

It is not clear what you want.
LEFT JOIN, INNER JOIN, ????
What is "AND Table2.Field3='XXX'" ??? Is it a criteria ???

 

[AndrewB]

It is not clear what you want.
LEFT JOIN, INNER JOIN, ????
What is "AND Table2.Field3='XXX'" ??? Is it a criteria ???

D'oh!
I meant LEFT JOIN.

For the other question, yes, it is a criteria of Field 3 = "XXX"

 

[MStef]

OK, Look at "DemoLeftJoinA2000.mdb"
Query1.

 

[AndrewB]

OK, Look at "DemoLeftJoinA2000.mdb"
Query1.

MStef,
Thanks for this. Your query shows my problem as it returns one record (where the Table 2 record has XXX in Field f31). I need to output all records from Table 1 together with the Table 2 info where the Field f31 = XXX.

I would expect to see the following results

f1.......f11.......f21........f31
A
B
C........C..........200........XXX

If I understand what I've seen before correctly, I need to code LEFT JOIN ON....AND.... rather than use a WHERE.
Can you (or anyone else) confirm?

Thanks!

 

[MStef]

OK, in that case you need another query.
I made Query2 and Query3. Open Query3.

 

[AndrewB]

OK, in that case you need another query.
I made Query2 and Query3. Open Query3.

Right - an extra step looks like a good idea. I'll go and apply this to my real DB - not just the Table 1, Table 2 stuff I've been posting here.

Thanks again.
Andrew

 

[grnzbra]

You don't have "WHERE" in the "WHERE" clause.