dPdms dPdms - 7 months ago 25
SQL Question

select query -mysql

If I have a table such as:

name1 | name2 | id |
+----------------+--------------+-----------+
| A | E | 1 |
| A | F | 1 |
| B | G | 1 |
| C | H | 1 |
| D | I | 1 |
| A | J | 2 |
| B | K | 2 |
| C | L | 2 |
| D | M | 2 |
| A | N | 2 |


what I need is that select all rows of id where name2 <> 'E'

If I do:

selete * from table where name2 <> 'E'


It only gives me this

name1 | name2 | id |
+----------------+--------------+-----------+
| A | F | 1 |
| B | G | 1 |
| C | H | 1 |
| D | I | 1 |
| A | J | 2 |
| B | K | 2 |
| C | L | 2 |
| D | M | 2 |
| A | N | 2 |


The result I want is (excluding all rows of id which contains name2 = 'E' at least once) :

name1 | name2 | id |
+----------------+--------------+-----------+
| A | J | 2 |
| B | K | 2 |
| C | L | 2 |
| D | M | 2 |
| A | N | 2 |


Which query should I use?

Answer

One approach is to use subquery that finds all ids that have a value 'E' in column name2 and then filter out all these ids:

SELECT * 
FROM table 
WHERE id NOT IN 
    ( SELECT DISTINCT id FROM table WHERE name2 = 'E' )