CODEWITHSUNDEEP
sql
user6561572
user6561572

Reputation: 339

How to merge SQL queries "NOT IN" and "IN"?

Suppose I have three collections C1, C2 and C3 and I want get all the elements in C3 that are in C1 but not in C2.

I tried this:

SELECT * 
FROM C3 
WHERE MyId IN (C1) 
  AND NOT IN (C2)

but I get this error:

Syntax error: Encountered "IN" at line 1, column 161

Any suggestions?

Thanks

Upvotes: 0

Views: 43

Answers (1)

paxdiablo
paxdiablo

Reputation: 881113

Assuming C3 is a table and C1/C2 are lists, you need:

SELECT * FROM C3 WHERE MyId IN (C1) AND MyId NOT IN (C2)
                       \__________/     \______________/

Each sub-condition joined with and or or must be complete, even if it's the same column. For example,

select name from people
    where gender in ('m','f')
    and age not in (1, 3, 5, 7, 9)

Upvotes: 2

Related Questions