asked on

SQL Non Unique ID on Left joined with Non Unique ID on Right. Need 1 to 1 join.

Table1
1 01/01/2000
1 02/01/2000
1 03/01/2000

Tabel2
1 01/01/2001
1 02/01/2001
1 03/01/2001
1 04/01/2001
1 05/01/2001
1 06/01/2001

What I would like to do is take the values of Table1 and match the non unique id (in this case it's '1') with the earliest date to the earliest dates in Table2. It should be a 1 to 1 relationship.

Example of desired output: (Table1 values = Table2 Values)
1 01/01/2000 = 1 01/01/2001
1 02/01/2000 = 1 02/01/2001
1 03/01/2000 = 1 03/01/2001

So I need the cardinality of Table1(3 items) to match the output and ignore the other values.
I'd like to do this without pointers.

Jim Horn

Not getting how 2000 = 2001, but along the lines of 'earliest dates', copy-paste the below into your SSMS, execute to verify it meets your requirements, then modify to work with your object names

CREATE TABLE t1 (id int, dt date)

INSERT INTO t1 (id, dt) 
VALUES 
 (1, '01/01/2000'), (1, '02/01/2000'), (1, '03/01/2000')

CREATE TABLE t2 (id int, dt date)

INSERT INTO t2 (id, dt) 
VALUES 
 (1, '01/01/2000'), (1, '02/01/2000'), (1, '03/01/2000'),
 (1, '04/01/2000'), (1, '05/01/2000'), (1, '06/01/2000')


SELECT table_1.id, table_1.dt
FROM (
	SELECT id, dt, RANK() OVER (PARTITION BY id ORDER BY dt) rank_order
	FROM t1) table_1
JOIN (
	SELECT id, dt, RANK() OVER (PARTITION BY id ORDER BY dt) rank_order
	FROM t2) table_2 ON table_1.id = table_2.id AND table_1.rank_order = table_2.rank_order
ORDER BY table_1.id, table_1.rank_order

Open in new window

ASKER CERTIFIED SOLUTION

Brian Crowe

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Vitor Montalvão

ButtonDownBobby, do you still need help with this question?