10 animals in 3 houses. Help me find the house that doesn't have a cow.

[phproy]

Hi guys,

I have 10 animals in 2 houses.
I have cleverly labelled the houses '1', '2' and '3'.
Each of the 10 animals in the house has an id.
Below is the SQL record:

id | animal | house

1 | dog | 1
2 | cat | 1
3 | cow| 1
4 | fish| 1
5 | dog | 2
6 | cat | 2
8 | fish| 2
9 | cat | 3
10| fish| 3

How can I use ONE SQL statement to get the house that has a 'dog' or a 'cat' but doesn't have a 'cow' ?

It should return the value '2' because only that house satisfies that condition.

[Drakla]

No, it should return 2 and 3 if it can have a dog OR a cat but now cow

PS - are you allowed to use a subquery, or is this MySQL, and before the change?

Without subqueries:

SELECT h1.house
FROM animalhouse h1
LEFT JOIN animalhouse h2
ON (h1.animal = 'cat' OR h1.animal='dog')
WHERE
h1.id != h2.id AND h1.house = h2.house
GROUP BY h1.house
HAVING SUM(h2.animal='cow') = 0
;

[phproy]

Youre right. 2 and 3 should appear as the result.

You see the problem is that Im using ADODB (http://adodb.sourceforge.net/)
I can only use very generic SQL query to maintain portability.
I don't think I can use subquery like the one you suggested.:bemused:

[Drakla]

I've edited it above, no subqueries and pretty much as standard as you can get.