Mysql SELECT narrowing search

Question

EDIT:That was fast. The reason why I have this is because the table is a pivot table between 2 tables one has "id" as primary key and the other "type" primary key

Hello.

I want the following:

Find only find "id" where "type" is 1 AND 2 AND 3

This is not working:

SELECT * FROM `table` WHERE `type` = 1 AND `type` = 2 AND `type` = 3;

The SELECT statment should only return one row (id = 1)

Table

id type
1  1
1  2
1  3

2  1
2  2

3  3

Answer 1

If I understand you correctly, couldn't you just add LIMIT 1 at the end of your query?

Answer 2

You're doing something wrong. You should not have multiple rows with the same id. It destroys the point of having one. Your id column should be a primary key that auto increments.

I think you should go back and reexamine your schema. Or at least add more detail about what you're trying to do.

Answer 3

A row can't have a column with a value 1 AND 2 AND 3 at the same time. It's like asking you if you're "20 and 21 and 22 years old". You can only be one of them.

You'd want to do something akin to:

SELECT id WHERE type = 1
INTERSECT
SELECT id WHERE type = 2
INTERSECT
SELECT id WHERE type = 3

but MySQL doesn't support INTERSECT, so you have to do it by hand:

SELECT id FROM table WHERE
    id IN (SELECT id FROM table WHERE
             id IN (SELECT id FROM table WHERE type = 3)
             AND type = 2)
    AND type = 1

Answer 4

Your search conditions are incompatible. Field 'type' can't be at the same time equal to 1, 2 and 3. Given the absence of INTERSECT in MySQL, you can join the same table two times:

SELECT id FROM 
  Table t1 JOIN Table t2 ON (t1.id=t2.id) JOIN Table t3 ON (t3.id=t2.id)
WHERE t1.type=1 AND t2.type=2 AND t3.type=3

This will build a cartesian product of rows with the same id and retain only those which have all three types you want.

Learning the basics of SQL can help you a lot. There is a lot of information around. (including MySQL docs)

Answer 5

If you only want to know the Id then add the keyword Distinct and just select the id, where there are records for the three different types ...

  Select Distinct id 
  FROM `table` t
  WHERE  Exists (Select * From Table Where id = t.id and Type = '1')
     And Exists (Select * From Table Where id = t.id and Type = '2')
     And Exists (Select * From Table Where id = t.id and Type = '3')

If you want to see the Id and the type then add the type to the select,

 Select Distinct id, Type 
  FROM `table` t
  WHERE  Exists (Select * From Table Where id = t.id and Type = '1')
     And Exists (Select * From Table Where id = t.id and Type = '2')
     And Exists (Select * From Table Where id = t.id and Type = '3')

if you want to see every row that has that id, then leave out the distinct

  Select id, Type 
  FROM `table` t
  WHERE  Exists (Select * From Table Where id = t.id and Type = '1')
     And Exists (Select * From Table Where id = t.id and Type = '2')
     And Exists (Select * From Table Where id = t.id and Type = '3')

Answer 6

Your selecting for the impossible. For each row, type can not be three things at once.

Answer 7

You can do it using intersection of sets

SELECT id FROM table WHERE type = 1
  INTERSECT
SELECT id FROM table WHERE type = 2
  INTERSECT
SELECT id FROM table WHERE type = 3

I am thinking if there is also simplier query but now I have no idea

Answer 8

I like tordek's answer the best, but here is a sloppy example of how to use group_concat in mysql to pull off similar matching.

The advantage of this is that you don't have to change it too much (other than altering the having) if you need to match MORE types later. The downside of course is that you cannot index the pseudo-column inthe select, which may or may not make a difference depending on your application.

You can also create the entire query (minus the having) as a view and then perform a select on it anytime you need. The view will not be writable, but if it fits your needs, yay! Then filter it using WHERE type LIKE...etc ;)

SELECT DISTINCT id, CONCAT( " ", GROUP_CONCAT( type SEPARATOR " " ), " " ) as type
     FROM table
GROUP BY id
HAVING type LIKE "% 1 %" AND type LIKE "% 2 %" AND type LIKE "% 3 %"

Wrap this in another subselect like so to get just the id.

SELECT id
    FROM(
         SELECT DISTINCT id, CONCAT( " ", GROUP_CONCAT( type SEPARATOR " " ), " " ) as type
             FROM table
         GROUP BY id
         HAVING type LIKE "% 1 %" AND type LIKE "% 2 %" AND type LIKE "% 3 %"
  ) t