Home > Software design >  Joining two tables where keys from one table is missing
Joining two tables where keys from one table is missing

Time:06-23

I am not sure what type of join I need to obtain my desired result. As per the below example I am trying to join table 1 to table 2 but I am missing Q3 in the first table.

table 1:

Quarter Sales
Q1 100
Q2 200
Q4 300

table 2:

Quarter Cost
Q1 20
Q2 50
Q3 70
Q4 10

I want to join both tables such that I get the following result:

Result:

Quarter Sales Cost
Q1 100 20
Q2 200 50
Q3 NULL 70
Q4 300 10

I have tried to use a FULL OUTER JOIN on Quarter from each table. But I think because I'm selecting table1.Quarter in the SELECT statement, I am getting NULL where Q3 should be. So essentially, I am getting the following which is incorrect:

Quarter Sales Cost
Q1 100 20
Q2 200 50
NULL NULL 70
Q4 300 10

Do I simply need to use the correct JOIN type, or is the problem a bit more complex than that?

CodePudding user response:

Join with a synthesized table that has all the quarters.

SELECT q.quarter, s.sales, c.cost
FROM (
    SELECT 'Q1' AS quarter
    UNION
    SELECT 'Q2'
    UNION
    SELECT 'Q3'
    UNION
    SELECT 'Q4'
) AS q
LEFT JOIN table1 AS s ON a.quarter = b.quarter
LEFT JOIN table2 AS c ON a.quarter = c.quarter
  • Related