CODEWITHSUNDEEP
mysql
Chan
Chan

Reputation: 1969

SELECT user who has some values

Data

name 'chan' value 'a'
name 'chan' value 'b'
name 'max' value 'a'
name 'max' value 'b'
name 'tony' value 'a'
name 'tony' value 'c'

I need to find out user who both have value a and b, this is my solution:

SELECT * FROM `table`
GROUP BY `name`
HAVING SUM(IF(`value` = 'a', 1, 0)) >= 1 AND SUM(IF(`value` = 'b', 1, 0)) >= 1

Any better way?

Upvotes: 3

Views: 69

Answers (2)

Gordon Linoff
Gordon Linoff

Reputation: 1271241

Your solution is ok, but it would be better written as:

SELECT name
FROM `table`
GROUP BY `name`
HAVING SUM(`value` = 'a') >= 1 AND SUM(`value` = 'b') >= 1;

A possibly more efficient form is:

SELECT name
FROM `table`
WHERE value in ('a', 'b')
GROUP BY `name`
HAVING COUNT(DISTINCT value) = 2;

And, depending on your data structure and indexes and size, this could also be efficient:

select ta.name
from table ta join
     table tb
     on ta.name = tb.name and ta.value = 'a' and tb.value = 'b';

I prefer the methods using group by and having because they generalize to a more diverse set of conditions.

Upvotes: 5

John Woo
John Woo

Reputation: 263943

Try this out. a DISTINCT is required if name per value in not unique.
eg (COUNT(DISTINCT value) = 2)

SELECT name
FROM tableName
WHERE value IN ('a', 'b')
GROUP BY name
HAVING COUNT(*) = 2

Upvotes: 1

Related Questions