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

User · Accepted 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

User · Answer

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

[sql] How to write subquery inside the OUTER JOIN Statement

Examples related to sql