How to select distinct based on multiple values for one column



I feel this is a very easy task and I am grossly overthinking it. I have looked and looked nothing seems to answer my question. My question is simple, how do I select distinct on one column, based on multiple values of another column:

Table:

Customers Fruit
Bob Orange
Bob Apple
Sally Orange
Sally Lemon
Sam Lemon
Sam Apple
Tom Orange
Tom Lime

The table above shows a new line every time a customer purchases a fruit. How do I select distinct for customers who either purchased an (Orange AND Apple) OR (Orange AND Lemon).

The end result should only show: Bob and Sally.

Answer

Assuming you don’t care if a user has all 3 you still want them returned. or if they have more than 2 but at least orange and one of the other 2.

Just using a simple join. Get those that are Orange and those that are either Apple or lemon–> join–> distinct.

SELECT Distinct A.Customers
FROM dbo.yourTable A
INNER JOIN dbo.yourTable B
 on A.Customer = B.Customer
WHERE A.Fruit = 'Orange'
  and B.Fruit in ('Apple','Lemon')

Assuming an index on dbo.yourTable.Fruit… should be pretty quick.



Source: stackoverflow