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