Left outer join (join between three tables)?
I have a main table that I need to get data from. I have a left outer join where the fields will fit 40% of the time. And then I have another connection where I need to map data from table A with.
This is pseudo-code SQL. This request will not work.
- This is the part I want to do, but it doesn't work. And H.COL3 = A.STATE ????
I am working with IBM DB2.
SELECT DISTINCT
APP_NO as app_no,
A.STATE as state
...
... Fields
...
FROM
TABLE_A A
LEFT OUTER JOIN
TABLE_B HIST
ON
HIST.COL1 = A.COL1
, TABLE_C B
LEFT OUTER JOIN
TABLE_D H
ON
H.COL2 = B.COL2
-- This is the part I want to do but doesn't work.
AND
H.COL3 = A.STATE????
WHERE
A.BRANCH = 'Data'
source to share
I think you could rewrite it like this (but I might misread your statement)
FROM
TABLE_A A LEFT OUTER JOIN TABLE_B HIST ON
HIST.COL1 = A.COL1
LEFT OUTER JOIN TABLE_D H ON
H.COL3 = A.STATE
LEFT OUTER JOIN TABLE_C B ON H.COL2 = B.COL2
WHERE
A.BRANCH = 'Data'
Also, the IBM doco states for this error:
An ON clause associated with a JOIN or MERGE statement is not valid. Explanation:
Column references in an ON clause must only refer to columns in tables that are in the scope of the ON clause.
So I might be wrong, it looks like an erronous ON clause when the outer join "H.COL3 = A.STATE" goes outside the scope of the On clause, because table A is not in that scope.
source to share