sql - How to write subquery inside the OUTER JOIN Statement

Question

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 1

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