CODEWITHSUNDEEP
sql
YROjha
YROjha

Reputation: 782

How to write subquery inside the OUTER 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.

Upvotes: 35

Views: 178027

Answers (2)

padmanvg
padmanvg

Reputation: 7

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

Upvotes: 0

James Anderson
James Anderson

Reputation: 27478

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

Upvotes: 67

Related Questions