SQL show only results that have both items

Question

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

Which DBMS product are you using? "SQL" is just a query language used by all relational databases, not the name of a specific database product. Please add a [tag](https://stackoverflow.com/help/tagging) for the database product you are using. [Why should I tag my DBMS](https://meta.stackoverflow.com/questions/388759/) — , Mar 02 '22 at 14:28

dcp · Answer 1 · 2022-03-02T14:38:27.023

0

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)

edited Mar 02 '22 at 14:38

answered Mar 02 '22 at 14:29

dcp

54,410
22
144
164

score 0 · Answer 2 · answered Mar 02 '22 at 14:31

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

score 0 · Answer 3 · answered Mar 02 '22 at 14:32

0

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');

answered Mar 02 '22 at 14:32

Zakaria

4,715
2
5
31

score 0 · Accepted Answer · 2022-03-02T14:44:47.823

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

SQL show only results that have both items

4 Answers4