I have a SQL table with names and fruits that the persons ate.
I only want the result to show the Names of people who ate both an apple and a banana. But if i use "Where Item=’Banana’ and item=’Apple’, the data shows nothing.
If i use: Where item in(‘banana’,’apple’), it shows the result as shown in the table.
| Name | Item |
|---|---|
| John | Apple |
| David | Banana |
| John | Banana |
>Solution :
We can do a GROUP BY and then select only the person who has eaten 2 fruits from the list, ie both of them
CREATE TABLE eaten( person VARCHAR (10), fruit VARCHAR (10));
INSERT INTO eaten VALUES ('John','apple'), ('Bill','apple'), ('Bill','pear'), ('David','banana'), ('David','banana'), ('John','banana');
SELECT person FROM eaten WHERE fruit IN ('apple','banana') GROUP BY person HAVING COUNT(DISTINCT fruit) =2;
| person | | :----- | | John |
db<>fiddle here