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