exclusion from normalized table query

id_galery color

1 blue
1 red
1 green

2 blue
2 red



this would return id_galery 1 and 2, exactly what I want, tested.

select id_galery,count(id_galery),
from galery_optional_inf
where color in ('blue','red')
group by id_galery
having count(color) = 2


How to exclude 1 from resultset as it contains green ?

??? Easy:


SELECT G1.id_galery, G1.theCount
FROM
( select id_galery,count(*) AS theCount
from galery_optional_inf
where color in ('blue','red')
group by id_galery
having count(*) = 2 ) AS G1
LEFT JOIN galery_optional_inf AS G2
ON G1.id_galery = G2.id_galery AND G2.color = 'green'
WHERE G2.id_galery IS NULL

Thanks.

I was imagining that there could be one litle simple sql (-:

with out any joins.

I imagine red part would work, or not ? Not I think.


SELECT G1.id_galery, G1.theCount
FROM
( select id_galery,count(*) AS theCount
from galery_optional_inf
where color in ('blue','red')
group by id_galery
having count(*) = 2 ) AS G1
LEFT JOIN galery_optional_inf AS G2
ON G1.id_galery = G2.id_galery AND G2.color in ('green','purple',...)
WHERE G2.id_galery IS NULL


EDIT: As far as I tested, this should work.

Yes, it should work fine.

You could also do
... AND G2.color NOT IN ('blue','red')