How to Left Join with a literal connector 1

[Griffyn]

Hi all,

I'm trying to get a query showing which records are missing from a table. In my SQL below, table T1 contains a list of records, and table T2 contains those same records, but with an ID field. T2 contains the records in T1 for different IDs, but not every ID is represented (of course), and each ID may not have every record in T1.

This is the query I'm trying to do:

SELECT T1.Field1, T2.ID
FROM T1 LEFT JOIN T2
ON (T1.Field1 = T2.Field2) AND (T2.ID = 1)
WHERE T2.ID Is Null

The query fails because Access reports 'Join expression not supported' and points to the literal '= 1' bit.

But this is what I need. A list of records in T1 that aren't found in T2 but only considering those records where the ID field is 1 (in this example).

I can't embed T2 in a subquery, because the query will be drawn upon in another program (Delphi) so any parameters have to be able to specified in the 'outer layer' so to speak.

I've thought of trying to get 1 appearing as a calculated field in T1, so that I can do a valid join, but can't get that happening either.

Can anyone show me how to do this?

 

[PHV]

Something like this ?
SELECT T1.Field1, A.ID
FROM T1 LEFT JOIN (
SELECT Field2, ID FROM T2 WHERE ID = 1
) A ON T1.Field1 = A.Field2
WHERE A.ID Is Null

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Griffyn]

Hooray! Wonderful. Thank you.