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