CODEWITHSUNDEEP
mysqlsql
mrid
mrid

Reputation: 5796

select records with matching criteria in different rows

Suppose I have a SQL table table1

+--------------------------+
| id | person |   eats     |
+----+--------+------------+
|  1 |   1    |   apple    |
|  2 |   3    |  banana    |    
|  3 |   2    |  orange    |
|  4 |   1    | strawberry |
|  5 |   2    |   grapes   |
|  6 |   4    |   apple    |
+----+--------+------------+

I want to get all persons who eat both apple and say strawberry.

I tried

select person 
from table1
where eats in( 'apple', 'strawberry' )

but this query returns 1, 1, 4, which I guess is because, it is checking against apple and strawberry seperately.

How can get all persons who eat both apple and strawberry ?

Upvotes: 1

Views: 110

Answers (2)

Alex
Alex

Reputation: 17289

SELECT person 
FROM table1
WHERE eats IN( 'apple', 'strawberry' )
GROUP BY person
HAVING COUNT(DISTINCT eats)=2

Update To me the query explains itself. But since you ask for explanation lets try together:

your original query return this result:

1
1
4

But you don't want to get 1 twice, that means you should group result by person, that bring us to next query:

SELECT person 
FROM table1
WHERE eats IN( 'apple', 'strawberry' )
GROUP BY person

which will return:

1
4

but you don't want person=4 because it only match apple, so the simpliest way to reach that to me is to count distinct eats like:

SELECT person, count(distinct eats)
FROM table1
WHERE eats IN( 'apple', 'strawberry' )
GROUP BY person

then we have this result:

person  count(distinct eats)
1        2
4        1

and we are ready to filter this resultset to get only those who have 2 (apple and strawberry):

SELECT person, count(distinct eats)
FROM table1
WHERE eats IN( 'apple', 'strawberry' )
GROUP BY person
HAVING count(distinct eats) = 2

this will return:

person  count(distinct eats)
1        2

But you did not ask for count that is why I've removed count(distinct eats) part from SELECTed fields list.

http://sqlfiddle.com/#!9/ea612/6

Upvotes: 5

juergen d
juergen d

Reputation: 204746

Group by person and take only those having both records in the group

select person 
from table1
where eats in( 'apple', 'strawberry' )
group by person
having count(*) = 2

Upvotes: 3

Related Questions