Reputation: 1969
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
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
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