SQL show only results that have both items-CodePudding

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

CodePudding user response：

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

CodePudding user response：

WITH CTE_Apple AS 
(
    SELECT * FROM Table where Item = 'Apple'
)
,
CTE_Banana AS 
(
    SELECT * FROM Table WHERE Item = 'Banana'

)
SELECT 
           A.Name 
FROM 
           CTE_Apple AS A
    JOIN
           CTE_Banana AS B ON B.Name = A.Name

Basically create two sub-sets of the data and join them together where the data intersects. There are plenty of other ways to do this but I find CTE (Common Table Expressions) the most elegant

CodePudding user response：

Using a subquery should work in any RDBMS:

select distinct Name 
from table_name
where Item = 'Apple'
and Name in 
(select Name from table_name where Item = 'Banana');

CodePudding user response：

You can use DISTINCT with an EXISTS clause to get what you need.

SELECT DISTINCT name
           FROM table1 a
          WHERE a.Item = 'Apple'
            AND EXISTS (SELECT *
                          FROM table1 b
                         WHERE b.Item = 'Banana'
                           AND b.Name = a.Name)