Select with condition

Question

Select with condition

Navigation

#1 by (3 votes)

0

I have the following sample 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 ONLY the places that have EXCLUSIVELY the color RED , ie, has another LOCAL that is not just 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 to use the following syntax:

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

But it did not work, and I'm out of ideas.

mysql sql sintaxe

asked by anonymous 03.05.2018 / 21:22

1 answer

Insert in the value of an input type="date" the date returned from the Select of the database Error: Notice: Undefined index || PHP || MYSQL ||

score 3 · Accepted Answer

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 have taken the @Barbetta SQLFiddle: link

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