YROjha YROjha - 5 months ago 7
SQL Question

How to write subquery inside the INNER JOIN Statement

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.

Answer

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 != '' 
Comments