...

View Full Version : exclusion from normalized table query



BubikolRamios
11-10-2011, 08:40 AM
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 ?

Old Pedant
11-10-2011, 09:09 AM
??? 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

BubikolRamios
11-10-2011, 09:14 AM
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.

Old Pedant
11-10-2011, 07:59 PM
Yes, it should work fine.

You could also do

... AND G2.color NOT IN ('blue','red')



EZ Archive Ads Plugin for vBulletin Copyright 2006 Computer Help Forum