Select on condition

Question

I have the following example table (tbl_Local) in Mysql:

ID| LOCAL|  COR
1 | A    |RED
2 | A    |RED
3 | B    |RED
4 | B    |BLACK
5 | B    |WHITE
6 | C    |RED
7 | D    |BLUE
8 | E    |BLUE
9 | E    |ORANGE
10| E    |YELLOW
11| E    |RED
12| F    |RED
13| G    |ORANGE
14| G    |BLUE
15| H    |ORANGE

I would like to create a select that returns to me ONLY the places they own EXCLUSIVELY the color RED, that is, if the LOCAL have another COR other than the RED it should not be displayed.

In this table for example the return should be:

    LOCAL|  COR
    A    |RED
    C    |RED
    F    |RED

I tried using the following syntax:

 Select tbl_Local.*, count(distinct(COR)) from tbl_Local where COR = 'RED' Group By LOCAL

But it didn’t work, and I’m out of ideas.

Answer 1

One option is not exists:

Select distinct 
 t.Local,
 t.COR
from tbl_Local t 
Where t.COR = 'RED' 
and not exists (select 1 from tbl_Local x where x.LOCAL = t.LOCAL and x.COR != t.COR);

I took advantage of @Barbetta’s Sqlfiddle: http://sqlfiddle.com/#! 9/82262e/10

Select all where the color is red and there is no same place with different color