How to write subquery inside the OUTER JOIN Statement

sql

Solution

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

Problem

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.

Original source