CODEWITHSUNDEEP
sqlms-access
Jason Lan
Jason Lan

Reputation: 3

Find all customers that did business with all three companies

I have a transactions table that contains customer names and the company they did business. There are three different company that the customers could have done business with A, B, C. How would I query for only customers that have done business with all three customers before?

Sample Table

A query that would only return Jack.

Upvotes: 0

Views: 100

Answers (2)

Olivier Jacot-Descombes
Olivier Jacot-Descombes

Reputation: 112324

If the records are unique by Customer Name / Company, you can use @GordonLinoff's answer, otherwise ensure they are unique with a subquery:

select [Customer Name]
from (select distinct [Customer Name], Company
      from t
      where Company in ("A", "B", "C")) x
group by [Customer Name]
having count(*) = 3;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269673

You can use group by and having:

select [Customer Name]
from t
where Company in ("A", "B", "C")
group by [Customer Name]
having count(*) = 3;

Upvotes: 1

Related Questions