Selecting Unmatched Records Based On Multiple Fields

SELECT DISTINCT
FieldA,FieldB,FieldC,
FieldD,FieldE
FROM [Table2]
WHERE FieldA NOT IN (SELECT DISTINCT Field1 From [TABLE1]) OR
FieldB NOT IN (SELECT DISTINCT Field2 From [TABLE1]) OR
FieldC NOT IN (SELECT DISTINCT Field3 From [TABLE1]) OR
FieldD NOT IN (SELECT DISTINCT Field4 From [TABLE1]) OR
FieldE NOT IN (SELECT DISTINCT Field5 From [TABLE1])

Regards

Tonny Soeroso

Hey Cliff -

I'm at home now, but if memory strikes me, I've seen this done in a much simpler way.

First up - if you have MS ACCESS, use it with an odbc connection and you can use the query tool in there to experiment very easily (note for some reason access ADP projects - that connect directly to the database without using ODBC, for some reason don't use the same query tool) Access has a query wizard that will do what you need, and you can switch to the SQL view and use that code in your ASP page with little modification.


ok I think the query you are looking for is a combination of a join and a where statement that looks for a null value in the other table

this works because specifiying the value from the joined table will either return a value if there's a match, or it will return null, which is what you're looking for.

SELECT Table1.*
FROM Table1 LEFT JOIN table2 ON
(table1.[fielda] = table2.[fielda]
AND
table1.[fielda] = table2.[fielda]
and etc...
)
WHERE (((table2.[fielda]) Is Null));

My co-worker decided on another tact.

Thanks for the help, though.