I want to join two table CUSTMR and DEPRMNT.
My needed is: LEFT OUTER JOIN OF two or more Tables with subquery inside the LEFT OUTER JOIN as shown below:
Table: CUSTMR , DEPRMNT
Query as:
SELECT
cs.CUSID
,dp.DEPID
FROM
CUSTMR cs
LEFT OUTER JOIN (
SELECT
dp.DEPID
,dp.DEPNAME
FROM
DEPRMNT dp
WHERE
dp.DEPADDRESS = 'TOKYO'
)
ON (
dp.DEPID = cs.CUSID
AND cs.CUSTNAME = dp.DEPNAME
)
WHERE
cs.CUSID != ''
Here the subquery is:
SELECT
dp.DEPID, dp.DEPNAME
FROM
DEPRMNT dp
WHERE
dp.DEPADDRESS = 'TOKYO'
Is it possible to write such subquery inside LEFT OUTER JOIN?
I am getting an error when running this query on my DB2 database.
This question is related to
sql
You need the "correlation id" (the "AS SS" thingy) on the sub-select to reference the fields in the "ON" condition. The id's assigned inside the sub select are not usable in the join.
SELECT
cs.CUSID
,dp.DEPID
FROM
CUSTMR cs
LEFT OUTER JOIN (
SELECT
DEPID
,DEPNAME
FROM
DEPRMNT
WHERE
dp.DEPADDRESS = 'TOKYO'
) ss
ON (
ss.DEPID = cs.CUSID
AND ss.DEPNAME = cs.CUSTNAME
)
WHERE
cs.CUSID != ''
I think you don't have to use sub query in this scenario.You can directly left outer join the DEPRMNT table .
While using Left Outer Join ,don't use columns in the RHS table of the join in the where condition, you ll get wrong output
Source: Stackoverflow.com