SQL SELECT "pair" records-CodePudding

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.

CodePudding user response：

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)