SQL Join With an 'Unless' clause

Question

I'm not quite sure what this is called, but I've spent some time thinking about it and I'm not sure how to approach it. I'm sure it's really simple.

I have two tables, foo and bar. They're related like this:

Foo Table:

 | id | name
 --------------
 | 1  | blah
 | 2  | blarg
 | 3  | blag

Bar Table (the meaning of baz is irrelevant) :

 | fooId | baz |
 ---------------
 |   1   | 100 |
 |   1   | 94  |
 |   1   | 27  |
 |   2   | 94  |
 |   3   | 19  |

So, multiple Bars per Foo. I want to select all Foos where they have a baz of 94, unless they also have a baz of 100. So in the above case I only want to select the Foo with an id of 2.

I tried doing something along the lines of:

SELECT id FROM foo 
LEFT JOIN bar 
ON foo.id = bar.fooId
WHERE bar.baz = 94
AND bar.baz != 100

But obviously that only got me so far. I'm sure there's probably some kind of group by clause in here, but I'm not exactly sure what it should be.

Thanks in advance!

EDIT: In case anyone else has this problem, as @iu mentioned, the term for this is an anti-join.

Answer 1

select id from foo
inner join bar b1 on b1.fooId=foo.id and b1.baz=94
left outer join bar b2 on b2.fooId=foo.id and b2.baz=100
where b2.fooId is null

The technical term for this is an "anti-join". A normal inner join is an "equi-join".

Answer 2

SELECT id 
FROM foo f
JOIN bar b1 ON f.id = b1.fooId
LEFT JOIN bar f2 ON f.id = b2.fooId AND b2.baz = 100
WHERE b1.baz = 94
AND b2.fooid is null

Answer 3

It could be something like:

SELECT id FROM foo where exists ( select * from bar where foo.id = bar.fooId and bar.baz = 94) and not exists (select * from bar where foo.id = bar.fooId and bar.baz = 100)

Answer 4

SELECT id FROM foo 
LEFT JOIN bar 
ON foo.id = bar.fooId
WHERE bar.baz = 94
AND NOT EXISTS (
    SELECT bar2.fooId FROM bar bar2
    WHERE bar2.fooId = bar.foodI AND bar2.baz = 100)

Is how I would do that.

Answer 5

You can get the result you want with EXISTS and sub-queries:

SELECT id
FROM foo
WHERE EXISTS (SELECT fooId FROM bar WHERE fooId = foo.id AND baz = 94)
  AND NOT EXISTS (SELECT fooId FROM bar WHERE fooId = foo.id AND baz = 100)