CODEWITHSUNDEEP
sqlpostgresql-9.3
Makoto
Makoto

Reputation: 775

Fill missing values in SQL query

I have a current SQL request that works OK

SELECT
    *
FROM
    firstTab 
LEFT JOIN 
    secondTab ON firstTab.id = secondTab.refId 
LEFT JOIN
    thirdTab ON firstTab.id = thirdTab.refId 
WHERE 
    secondTab.colY IN ('value1', 'value2') 
    AND thirdTab.colZ IN ('value3', 'value4')

So far so good.

The problem is that I get the result only for rows when there are data for the values in colA or colB (which is the normal behaviour).

(currently I only get

         colY      colZ       currentResult
--------------------------------------------
row1    value1    value3          resultA
row3    value2    value3          resultB

)(normal behaviour)

I wish to do fill the missing values with some NULL values in such a way

         colY      colZ       currentResult
--------------------------------------------
row1    value1    value3          resultA
row2    value1    value4           NULL
row3    value2    value3          resultB
row4    value2    value4           NULL

('value1','value2') and ('value3','value4') are given , I know them in advance

I tried to "init" the result with a cartesian product with CROSS JOIN of secondTab and thirdTab but then I am stuck.

How can I achieve this?

Thanks in advance

Upvotes: 0

Views: 441

Answers (2)

MihaiM
MihaiM

Reputation: 199

You need to do an FULL OUTER JOIN:

Select *
FROM firstTab LEFT JOIN 
     secondTab
     ON firstTab.id = secondTab.refId FULL OUTER JOIN
     thirdTab
     ON firstTab.id = thirdTab.refId 
WHERE secondTab.colY in ('value1','value2') and
      thirdTab.colZ in ('value3','value4')

Join ideas

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269773

You need to move the filtering conditions to the ON clauses:

Select *
FROM firstTab LEFT JOIN 
     secondTab
     ON firstTab.id = secondTab.refId AND secondTab.colY in ('value1','value2') LEFT JOIN
     thirdTab
     ON firstTab.id = thirdTab.refId and thirdTab.colZ in ('value3','value4');

Unmatched values from the LEFT JOIN are NULL. The WHERE clause will filter these out, turning the outer joins into inner joins.

Upvotes: 2

Related Questions