I have a table that has 2 columns customer and city . there are 4 unique cities (w,x,y,z) I want customers that is associated with all cities. So for the table
+----------+------+ | Customer | City | +----------+------+ | A | W | | A | X | | A | Y | | A | Z | | B | X | | C | Y | | Z | X | | Z | Y | +----------+------+
the desired output is
+----------+------+ | Customer | City | +----------+------+ | A | W | | A | X | | A | Y | | A | Z | +----------+------+
Query I used was :
x
Select Customer,City
From Table As T
Where Exists (
Select 1
From Table As T2
Where T2.Customer = T.City
And T2.City<> T.City
)
Advertisement
Answer
With aggregation and the condition in the HAVING clause you can get the customers that you want:
select customer
from tablename
group by customer
having count(*) = (select count(distinct city) from tablename)
and you can use it with the operator IN:
select * from tablename
where customer in (
select customer
from tablename
group by customer
having count(*) = (select count(distinct city) from tablename)
)