CODEWITHSUNDEEP
mysqlsqlselect
dPdms
dPdms

Reputation: 173

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?

Upvotes: 1

Views: 110

Answers (4)

user5824530
user5824530

Reputation:

SELECT * FROM table WHERE id NOT IN (SELECT id FROM table WHERE name2 <> 'E')

Upvotes: 1

spencer7593
spencer7593

Reputation: 108370

We can use an anti-join pattern: 

SELECT t.name1
     , t.name2
     , t.id
  FROM mytable t
  LEFT
  JOIN mytable q
    ON q.id    = t.id
   AND q.name2 = 'E'
 WHERE q.id IS NULL

The left outer join operation says to return all rows from t, along with matching rows from q. The trick is the condition in the WHERE clause. For any rows that found a matching row in q, the value of q.id is guaranteed to be non-NULL (because of the equality comparison). Rows from t that didn't have a matching row will have a NULL value for q.id. So this returns only rows from t that didn't have a matching row from q.

Upvotes: 1

Bryan
Bryan

Reputation: 100

select * from table where name2 != 'E';

Upvotes: -2

dotnetom
dotnetom

Reputation: 24901

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' )

Upvotes: 5

Related Questions