CODEWITHSUNDEEP
mysqlsql
Marco
Marco

Reputation: 2737

Select where equal AND not equal

I have a table (n:m) named group, like this:

group_id    type
-------------------------
53          7
152         7
301         7
....        ...
53          12

EDITED:

I want to select all the rows where the group contains the type 7 but, if the group contains the type 12, i wan to exclude it

The output should be:

group_id    type
-------------------------
152         7
301         7

I've created a query

SELECT *
FROM group AS g
WHERE g.type= 7 AND g.type!= 12

But, i'm getting

group_id    type
-------------------------
53          7
152         7
301         7

Any idea why?

Upvotes: 3

Views: 2124

Answers (2)

Michael Berkowski
Michael Berkowski

Reputation: 270775

There are a few ways to do this. One is to perform a LEFT JOIN joining two instances of the table, limiting one side to type = 7 and the other to type = 12, and looking for a non-match on the side limited to type = 12.

SELECT
  g1.*
FROM
  `group` g1
   LEFT JOIN `group` g2 ON g1.group_id = g2.group_id AND g2.type = 12
WHERE
   g1.type = 7
   /* NULL means no match in the 12's */
   AND g2.group_id IS NULL

Here's a demonstration: http://sqlfiddle.com/#!2/2549ce/2

It can also be done with a NOT EXISTS, relating the subquery to the outer via group_id.

SELECT
  g.*
FROM 
  `group` g
WHERE
  g.type = 7
  AND NOT EXISTS (
    SELECT group_id FROM `group` g2 WHERE type = 12 AND g.group_id = g2.group_id
  )

http://sqlfiddle.com/#!2/2549ce/7

Upvotes: 6

Conrad Frix
Conrad Frix

Reputation: 52675

there are several ways to do this NOT IN is one

SELECT *
FROM group AS g
WHERE g.type= 7 
and group_id not in (select group_id from group where g.type = 12)

Upvotes: 5

Related Questions