SQL- Mutually exclusive JOIN based on reservation and item production date (user/item must not appea-CodePudding

I have two tables order and item and I need to match orders and items and the match is given by the column match and I need to make sure the customer who orders first will receive the item first (oldest reservation date gets the product first).

Also, the company prefers to send the oldest items first i.e oldest production date;

Practical example:

order table:

user	match	reservation_date
alan	vz123	01/22
maria	vz123	02/22
doug	vz123	03/22

item table

item_id	match	production_date
X	vz123	03/22
Y	vz123	02/22

The problem is when joining by the match column (item specs) I'm getting 1-N results;

The final result I want is:

user	item_id	reservation_date	production_date	match
alan	Y	01/22	02/22	vz123
maria	X	02/22	03/22	vz123

Note that neither user nor item_id appears in the result set more than once and we are giving preferences based on dates;

Company prefers to fulfill orders based on reservation date (oldest first)
Company prefers to sell items based on production date ("production gate date", oldest first)

Is this possible in SQL (SQL server, postgres...)?

CodePudding user response：

You can add a window function ROW_NUMBER, to determine which row of the order is relevant to the item row

One problem you need to adress, that you have to mark the rows that are already processed.

CREATE TABLE orders  (
  "user" VARCHAR(5),
  "match" VARCHAR(5),
  "reservation_date" VARCHAR(5)
);

INSERT INTO orders 
  ("user", "match", "reservation_date")
VALUES
  ('alan', 'vz123', '01/22'),
  ('maria', 'vz123', '02/22'),
  ('doug', 'vz123', '03/22');

CREATE TABLE item  (
  "item_id" VARCHAR(1),
  "match" VARCHAR(5),
  "production_date" VARCHAR(5)
);

INSERT INTO item 
  ("item_id", "match", "production_date")
VALUES
  ('X', 'vz123', '03/22'),
  ('Y', 'vz123', '02/22');

SELECT 
"user","item_id","reservation_date","production_date",o."match"
FROM
(SELECT *, ROW_NUMBER() OVER(PARTITION BY "match" ORDER BY  "reservation_date" ASC) rn FROM orders) o
JOIN (SELECT *, ROW_NUMBER() OVER(PARTITION BY "match" ORDER BY  "production_date" ASC) rn FROM item) i
ON i."match" = o."match" AND o.rn = i.rn

user  | item_id | reservation_date | production_date | match
:---- | :------ | :--------------- | :-------------- | :----
alan  | Y       | 01/22            | 02/22           | vz123
maria | X       | 02/22            | 03/22           | vz123

db<>fiddle here