Skip to content
Advertisement

SQL SELECT “pair” records

There are cities, suppliers, and customers tables.

Each supplier, (as well as customer), is located in one and only one city.

How could I select pairs of supplier and customer within the city, where each of them is mentioned only once? P.S. if there is no “pair” NULL should be instead. E.g.:

cityId supplierId customerId
1 1 1
1 2 2
1 3 NULL
2 4 3
2 NULL 4

So… having suppliers with ids 1, 2 are located in city 1 and customers with ids 1, 2 are located there as well:

With JOIN I receive “duplicated” pairs (e.g. 1-1, 1-2, 2-1, 2-2), here I ask how to make (1-1, 2-2) pairs.

Advertisement

Answer

You seem to want vertical lists of suppliers and customers. The problem is that you don’t have a join key. You can create one with row_number():

User contributions licensed under: CC BY-SA
5 People found this is helpful
Advertisement