Select ... where ... Not in for 2 values

[wrbodine]

Hi,

I have a query bringing back two values that are connected, but not directly in a lookup table. (There is a many to many relationship between one of the values and a value that's connected to the other one).

Is there a way to use a NOT IN clause for two values, or something that would have the same effect?

I.E. :

Select Customer, Seminar from .... (long from clause involving 4 tables) where Customer, Seminar NOT IN
(Select Customer, Seminar from ..... (lots of joins, 4 tables, brings back the ones I want to exclude)

I hope this makes sense; I have a query with all possible combinations, and I have a subquery with the values I want to exclude, but there's no ID that directly applies to these values, they're each unique by the customer and seminar values together.

Thanks for any advice,
Ray

 

[geopat]

You may use LEFT JOIN.

Select Customer, Seminar from .... (long from clause involving 4 tables)
LEFT JOIN
(Select Customer, Seminar from ..... (lots of joins, 4 tables, brings back the ones I want to exclude) AS RESULT1
ON TABLEA.CUSTOMER = RESULT1.CUSTOMER AND TABLEA.SEMINAR = RESULT1.SEMINAR
/* NOTE: BECAUSE I DON'T KNOW YOUR TABALES' NAME, I JUST CALL IT TABLEA */
WHERE RESULT1.CUSTOMER IS NULL

 

[cmmrfrds]

What about concatenating customer and seminar in the first query? I didn't try it but it seems like it should work.

Select (Customer + Seminar) as custSem from .... 4 tables)

AND custSem NOT IN
(Select (Customer + Seminar) as custSem from ..... 4 tables)

 

[wrbodine]

Thanks so much for the ideas; I ended up doing something similar to cmmrfrds solution here, and having 2 queries that I called from another query and used the "NOT IN" clause. I joined the 2 fields together in the queries....

Thanks again,
Ray