Skip to content
Advertisement

SQL query to get Column A with all Column B

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)
)
User contributions licensed under: CC BY-SA
6 People found this is helpful
Advertisement