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.

CREATE TABLE Cities (
    Id int IdENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Name nvarchar(100) NOT NULL UNIQUE,
)

CREATE TABLE Suppliers (
    Id int IdENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Name nvarchar(100) NOT NULL UNIQUE,
    CityId int NOT NULL,
    CONSTRAINT FK_Suppliers_Cities FOREIGN KEY (CityId)
        REFERENCES Cities (CityId)
)

CREATE TABLE Customers (
    Id int IdENTITY(1,1) NOT NULL PRIMARY KEY CLUSTERED,
    Name nvarchar(100) NOT NULL UNIQUE,
    CityId int NOT NULL,
    CONSTRAINT FK_Customers_Cities FOREIGN KEY (CityId)
        REFERENCES Cities (CityId)

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

select coalesce(s.cityid, c.cityid) as cityid,
       s.id as supplierid, c.id as customerid
from (select s.*, row_number() over (order by id) as seqnum
      from suppliers s
     ) s full join
     (select c.*, row_number() over (order by id) as seqnum
      from customers c
     ) c
     on s.cityid = c.cityid and s.seqnum = c.seqnum
order by cityid, coalesce(s.seqnum, c.seqnum)

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