SQL select from single SQL table where a value exists in multiple rows

Question

I have this table:

Fruit	Name
Apple	Abbey
Bananna	Abbey
kiwi	Charley
Apple	Bobby
grapes	Luke
orange	Beth
Apple	Eric
Mango	Charles
Pear	Dave
Bananna	Eric

This is sample data. The actual table has nearly a million rows.

I need to pull rows with names based on two data items. For example, I want to know who bought both banana and apple in one output, like so:

Fruit	Name
Apple	Abbey
Bananna	Abbey
Apple	Eric
Bananna	Eric

I tried this:

Select Fruit, name
From table
Where x = apple and x = banana

But it doesn't run. How can I do this?

Answer 1

You can use window functions, which in most cases are more efficient than self-joins or multiple subqueries.

If there are no duplicate fruit/name tuples, then:

select name, fruit
from (
    select t.*, count(*) over(partition by name) cnt
    from mytable t
    where fruit in ('Apple', 'Banana')
) t
where cnt = 2
order by name, fruit

Else:

select name, fruit
from (
    select t.*,
        max(case when fruit = 'Apple'  then 1 else 0 end) over(partition by name) has_apple,
        max(case when fruit = 'Banana' then 1 else 0 end) over(partition by name) has_banana
    from mytable t
    where fruit in ('Apple', 'Banana')
) t
where has_apple= 1 and has_banana = 1
order by name, fruit