CODEWITHSUNDEEP
sqlwhere-clause
JRii
JRii

Reputation: 23

SQL to exclude two columns

Should be simple, but cannot get query to work as I need.

Records:

parent item version
Main Child1 1
Main Child2 2
Main Child2 1
Main Child3 1

Expected results

parent item version
Main Child2 2
Main Child2 1
Main Child3 1

Tried with SQL scripts below without success:

SELECT item, version FROM product as i WHERE i.parent ='Main' AND 
i.item != 'Child1' AND i.version != 1


SELECT parent, item, version 
FROM product as i 
WHERE i.parent = 'Main' 
AND NOT EXISTS (SELECT item, version FROM s_product as n WHERE n.item = 'Child1' AND n.version = 1

Upvotes: 0

Views: 286

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 521239

Well your first attempt might be on the right track, but the logic is wrong. You want:

SELECT item, version
FROM product
WHERE parent = 'Main' AND
      NOT(item = 'Child1' AND version = 1);

By DeMorgan's Laws, this can be rewritten as:

SELECT item, version
FROM product
WHERE parent = 'Main' AND
      (item != 'Child1' OR version != 1);

Upvotes: 1

Related Questions