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.
Advertisement
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.