Mysql SELECT narrowing search

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

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

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

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

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)

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

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

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.