Menu
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.
602
Rewriting Subqueries as JOINSA subquery using IN can be rewritten with the DISTINCT keyword, for example: SELECT * FROM table1 WHERE col1 IN (SELECT col1 FROM table2); can be rewritten as: SELECT DISTINCT table1.
Inner Join will execute sub query only once.
Subqueries with the INSERT Statement Subqueries also can be used with INSERT statements. The INSERT statement uses the data returned from the subquery to insert into another table. The selected data in the subquery can be modified with any of the character, date or number functions. The basic syntax is as follows.
yes, sql works on sets, a subquery returns a set as result, so this is possible.
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 != '' If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
