Melding two tables

Database
Fan

Melding two tables

I have two tables that I need to "meld" together.

I want all the records from firsttable.  I need to link firsttable to secondtable by acct and get the date and user number from the second table.  The catch is user from the second table can only be used once.  First table account A has two records and in second table account A has two records.  User 5 should be tied to user 1 and user 2 should be tied to user 6.  I could do this with a for next loop and iterate through, but that is only supported in stored procedures and I need to run this in stand alone mode.

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 7486 StartFragment: 314 EndFragment: 7454 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

CREATE TABLE firsttable (ACCT CHAR(10), userno CHAR(10), dt DATE)UNIQUE PRIMARY INDEX (userno);

INSERT INTO firsttable SELECT 'A', '1', DATE'2018-01-01';
INSERT INTO firsttable SELECT 'A', '2', DATE'2018-01-02';
INSERT INTO firsttable SELECT 'C', '3', DATE'2018-01-02';


CREATE TABLE secondtable (ACCT CHAR(10), userno CHAR(10), dt DATE)UNIQUE PRIMARY INDEX (userno);

INSERT INTO secondtable SELECT 'A', '5', DATE'2018-01-02';
INSERT INTO secondtable SELECT 'A', '6', DATE'2018-01-02';
INSERT INTO secondtable SELECT 'B', '7', DATE'2018-01-02';

 I had thought I could use a qualify, but that doesn't doesn't address the second user.  Row  2 on the output should show 6 not 5.

Format:HTML Format Version:1.0 StartHTML: 165 EndHTML: 3375 StartFragment: 314 EndFragment: 3343 StartSelection: 314 EndSelection: 314SyntaxEditor Code Snippet

SELECT f.*, s.userno AS seconduser
FROM firsttable f
LEFT JOIN secondtable s ON f.acct=s.acct
QUALIFY ROW_NUMBER() OVER (PARTITION BY f.acct, f.userno ORDER BY s.dt, s.userno)=1

 A          1          01/01/2018 5        
A          2          01/02/2018 5        
C          3          01/02/2018 ?

 

I then thought about having a qualify on both sides, but that doesn't work either.

 

what I want is:

A ,1, 01/01/2018, 5

A, 2, 01/02/2018, 6

C, 3, 01/02/2018, null

1 REPLY
Junior Contributor

Re: Melding two tables

No QUALIFY, but two ROW_NUMBERs:

SELECT f.*, s.userno AS seconduser
FROM 
 (
   SELECT f.*, 
      Row_Number() Over (PARTITION BY acct ORDER BY dt) AS rn
   FROM firsttable f
 ) AS f
LEFT JOIN
 (
   SELECT s.*, 
      Row_Number() Over (PARTITION BY acct ORDER BY dt) AS rn
   FROM secondtable s
 ) AS s
ON f.acct=s.acct
AND f.rn = s.rn

You might need to change the ORDER BYs according to your business logic.