MySQL Use multiple conditionals on a group by clause?-CodePudding

I have a table of Neighborhoods, comprised of a neighborhood_id and a zip_code.

 -------------- --------- 
| Neighborhood | zipcode |
 -------------- --------- 
|            1 |   12345 |
|            2 |   12346 |
|            3 |   12357 |
 -------------- ---------

There are oneToMany Person records that relate to a neighborhood:

 -------- -------------- ----------- 
| Person | neighborhood | eye color |
 -------- -------------- ----------- 
|      1 |            1 | blue      |
|      2 |            1 | grey      |
|      3 |            1 | brown     |
|      4 |            2 | blue      |
|      5 |            2 | brown     |
|      6 |            3 | hazel     |
 -------- -------------- -----------

I am looking to identify neighborhoods where there is at least one person with blue eyes and no people with grey eyes (in this case, neighborhood=2 would meet the condition).

How would I structure the query to group by neighborhoods across rows?

CodePudding user response：

This is simply:

select neighborhood
from Person
where `eye color` in ('blue','grey')
group by neighborhood
having sum(`eye color`='grey')=0

CodePudding user response：

Try:

SELECT Neighborhood 
FROM Neighborhoods n  
WHERE EXISTS   ( SELECT neighborhood  
                 FROM My_table m
                 WHERE n.Neighborhood = m.neighborhood 
                 AND  eye_color = 'blue'
                 )
AND NOT EXISTS ( SELECT neighborhood  
                 FROM My_table m
                 WHERE n.Neighborhood = m.neighborhood 
                 AND  eye_color = 'grey'
                 );

Demo