SQL- How to combine 3 tables to get this result?-CodePudding

The SNO seems to be misdirecting So, I simplified the question even further

--creation
CREATE TABLE LAB (JOB_ID int, LAB_ID VARCHAR(50));
CREATE TABLE SPR (JOB_ID int, SPR_ID VARCHAR(50));

--Table 1 data insertion
INSERT INTO LAB (JOB_ID, LAB_ID) VALUES(10, 'LAB_10');
INSERT INTO LAB (JOB_ID, LAB_ID) VALUES(10, 'LAB_20');
INSERT INTO LAB (JOB_ID, LAB_ID) VALUES(10, 'LAB_30');

--Table 2 data insertion
INSERT INTO SPR (JOB_ID, SPR_ID) VALUES(10, 'SPR_10');
INSERT INTO SPR (JOB_ID, SPR_ID) VALUES(10, 'SPR_20');

The query I tried

SELECT L.JOB_ID, L.LAB_ID, S.SPR_ID
FROM LAB L 
JOIN SPR S ON S.JOB_ID = L.JOB_ID

The result I got

JOB_ID LAB_ID SPR_ID

10 LAB_10 SPR_20

10 LAB_10 SPR_10

10 LAB_20 SPR_20

10 LAB_20 SPR_10

10 LAB_30 SPR_20

10 LAB_30 SPR_10

The result I need

JOB_ID LAB_ID SPR_ID

10 LAB_10 SPR_10

10 LAB_20 SPR_20

10 LAB_30 NULL

CodePudding user response：

Check this:

WITH 
cte1 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY lab_id) rn
          FROM lab ),
cte2 AS ( SELECT *, ROW_NUMBER() OVER (PARTITION BY job_id ORDER BY spr_id) rn
          FROM spr ),
cte3 AS ( SELECT rn, job_id FROM cte1
          UNION 
          SELECT rn, job_id FROM cte2 )
SELECT cte1.job_id, cte1.lab_id, cte2.spr_id
FROM cte3
LEFT JOIN cte2 ON cte3.rn = cte2.rn AND cte3.job_id = cte2.job_id
LEFT JOIN cte1 ON cte3.rn = cte1.rn AND cte3.job_id = cte1.job_id