Given these two tables:
Foo (id, name) -- PK = id
Bar (fooId, value) -- PK = composite, fooId + value
-- value is a non-negative int
How can I find all the Foo.names where there is no corresponding Bar,value above 0?
eg:
Foo
id name
1 a
2 b
3 c
Bar
fooid value
1 0
1 1
2 0
3 4
Result:
b
SELECT Name
FROM Bar
INNER JOIN Foo ON Foo.Id = Bar.fooId
GROUP BY fooId, name
HAVING SUM(Value) = 0
This query and the one posted by zodeus take about the same length of time to execute, however this one will not include any Foo records which are not referenced by the Bar table. For example if you also had the record Foo ( 4, d ), this query would still return 'b', where as zodeus's query would return 'b' and 'd'.
I find the following the easiest to read and understand...
SELECT foo.name
FROM foo
WHERE NOT EXISTS (SELECT 'x'
FROM bar
WHERE bar.fooid = foo.id
AND bar.value > 0)
SELECT name FROM FOO WHERE id NOT IN(
SELECT fooId FROM Bar GROUP BY fooId HAVING MAX(Value) > 0)
select Foo.name
from Foo
where Foo.id not in
(select Bar.fooid from Bar
where value > 0)
