tags:

views:

38

answers:

3

How would I write a SQL query that excludes a record if one (external) record from a one to many join matches a certain condition?

For example:

Details
ID      
1         
2         

Items
DetailID    Item
1           A
1           B
2           B
2           C

How would I select the detail records where the Items do not include 'A'?

+4  A: 
SELECT *
FROM details d
WHERE NOT EXISTS ( 
  SELECT * 
  FROM items i
  WHERE i.DetailID == d.ID 
    AND i.Item = 'A')
Stefan Steinegger
A: 

Why not just using INNER JOIN like:

SELECT details.*
FROM details 
INNER JOIN items ON details.ID=items.DetailID AND items.Item<> 'A'
systempuntoout
That would return the second row in the items table. The OP wants to exclude detailId = 1 completely.
Daniel Vassallo
Is it that clear that OP wants it :)?Probably it's like you said.
systempuntoout
+1  A: 

building on systempuntoout's solution:

SELECT details.*
FROM details 
LEFT OUTER JOIN items ON details.ID=items.DetailID AND items.Item = 'A'
WHERE items.DetailID IS NULL
devio