CODEWITHSUNDEEP
mysqlsql
georg
georg

Reputation: 215039

join with a "not there" condition

I have a linkage table object_id -> property:

id  |  prop
----|-------
1   |  ham
1   |  baz
1   |  whatever

2   |  spam
2   |  ham
2   |  whatever

3   |  ham
3   |  quux

4   |  foo
4   |  spam

How can I select only ids that have ham but not spam (in this example: 1 and 3)? Do I have to resort to subqueries?

Upvotes: 0

Views: 78

Answers (2)

Kaf
Kaf

Reputation: 33839

Select id From table1 
Where prop = 'ham' and id not in (select id from table1 where prop = 'spam')

Upvotes: 1

zerkms
zerkms

Reputation: 255115

SELECT DISTINCT id
  FROM tbl o
 WHERE prop = 'ham'
   AND NOT EXISTS (SELECT null
                     FROM tbl i
                        WHERE o.id = i.id
                          AND i.prop = 'spam')

The same can be done using LEFT JOIN, but mysql query optimizer would produce similar execution plan for it. So it's a matter of preference on which to choose.

Bonus:

SELECT DISTINCT t1.id
  FROM tbl t1
  LEFT JOIN tbl t2 ON t1.id = t2.id
                  AND t2.prop = 'spam'
 WHERE t1.prop = 'ham'
   AND t2.id IS NULL

Upvotes: 2

Related Questions