CODEWITHSUNDEEP
mysqlsql
Collin
Collin

Reputation: 1877

SQL where particular column values appears

I wasn't sure how to really search for this..

Lets say I have a simple table like this

ID     Type
1      0
1      1
2      1
3      0
4      0
4      1

How could I select all ID's which have a type of both 0 and 1?

Upvotes: 0

Views: 54

Answers (3)

Andy_in_Van
Andy_in_Van

Reputation: 331

A more generalized way of doing this would by to use a CASE column for each value you need to test combined with a GROUP BY on the id column. This means that if you have n conditions to test for, you would have a column indicating if each condition is met for a given id. Then the HAVING condition becomes trivial and you can use it like any multi-column filter, or use the grouping as your subquery and the code looks simpler and the logic becomes even easier to follow.

SELECT id, Type0,Type1 
FROM (
     SELECT id, 
       Type0 = max(CASE WHEN type = 0 THEN TRUE END)
       , Type1 = max(CASE WHEN type = 1 THEN TRUE END)
     FROM t
     GROUP BY id
     ) pivot
WHERE Type0 = TRUE and Type1 = TRUE

Upvotes: 1

winmutt
winmutt

Reputation: 405

Having is pretty expensive and that query can't hit keys.

SELECT ID FROM foo AS foo0 JOIN foo AS foo1 USING (ID) WHERE foo0.Type=0 AND foo1.Type=1 GROUP BY foo0.id.

Upvotes: 2

Mihai
Mihai

Reputation: 26784

SELECT id,type
FROM t
GROUP BY id 
HAVING SUM(type=0)>0
AND SUM(type=1)>0

You just group by id ,than with HAVING you use post aggregation filtering to check for 0 and 1.

Upvotes: 4

Related Questions