Sql subquery for DB2

Question

The sql query requires 4 tables to be joined, which i did, and i have to display few columns out of them that satisfy a condition. Say this is the query in Where clause. Now how do I write a subquery.. to display another column (ORG_NAME,that is there in ORG_UNIT) whose contents is based on the rows that is satified by the query in Where clause. I wrote this code, but it is not working for me:

SELECT T33.CONTRACT_NUM, T135.MINOR_ORG_NUM, T96.ORG_TYPE,T22.CFD_FLAG,
  (SELECT T96.ORG_NAME
   FROM ORG_UNIT T96, SUB_UNIT T135
   WHERE T96.ORG_NUMBER IN (T135.MAJOR_ORG_NUMBER)) AS HEAD_ORG_NAME
FROM
   ORG_UNIT T96, SUB_UNIT T135, CUST_CONTRACT T33, CONT_ASSIGNMT T22
WHERE
   T96.ORG_NUMBER = T22.ORG_NUMBER
  AND T22.CTR_SYS_NUM = T33.CTR_SYS_NUM
  AND T96.ORG_NUMBER = T135.MINOR_ORG_NUMBER
  AND T135.RELTN_TYPE = 'HOS'
  AND T22.CFD_FLAG = 'Y'; 

For the record, T135 contains head offices numbers (MAJOR_ORG_NUMBER) and their sub - offices numbers (MINOR_ORG_NUMBER)

Answer 1

In SQL, use JOIN to "merge" together tables based on their a common columns.

Here is a simple guide that would give you the base idea: SQL JOIN

In SQL, it's always best to draw what you want to do, so refer to this link to see a "LEFT JOIN" picture example: LEFT JOIN

Using a "LEFT JOIN" to merge your tables (where : ORG_UNIT.ORG_NUMBER = SUB_UNIT.MAJOR_ORG_NUMBER), will look like this:

LEFT JOIN SUB_UNIT T135 ON T96.ORG_NUMBER = T135.MAJOR_ORG_NUMBER

In a query, you put a JOIN right after the "FROM", and BEFORE the "WHERE":

SELECT 
    T33.CONTRACT_NUM,
    T135.MINOR_ORG_NUM,
    T96.ORG_TYPE,
    T22.CFD_FLAG,
    T135.ORG_NAME AS HEAD_ORG_NAME
FROM
    ORG_UNIT T96,
    CUST_CONTRACT T33,
    CONT_ASSIGNMT T22

LEFT JOIN SUB_UNIT T135 ON T96.ORG_NUMBER = T135.MAJOR_ORG_NUMBER

WHERE
    T96.ORG_NUMBER = T22.ORG_NUMBER
    AND T22.CTR_SYS_NUM = T33.CTR_SYS_NUM
    AND T96.ORG_NUMBER = T135.MINOR_ORG_NUMBER
    AND T135.RELTN_TYPE = 'HOS'
    AND T22.CFD_FLAG = 'Y';

Notice, that you could (and SHOULD) use JOIN for merging all the tables (and avoiding using an expensive WHERE condition):

SELECT 
    T33.CONTRACT_NUM,
    T135.MINOR_ORG_NUM,
    T96.ORG_TYPE,
    T22.CFD_FLAG,
    T135.ORG_NAME AS HEAD_ORG_NAME

FROM
    ORG_UNIT T96

LEFT JOIN SUB_UNIT T135 ON
    T96.ORG_NUMBER = T135.MAJOR_ORG_NUMBER
    AND T96.ORG_NUMBER = T135.MINOR_ORG_NUMBER
LEFT JOIN  ON
    CONT_ASSIGNMT T22 ON T96.ORG_NUMBER = T22.ORG_NUMBER
LEFT JOIN ON 
    CUST_CONTRACT T33 ON T22.CTR_SYS_NUM = T33.CTR_SYS_NUM

WHERE
    T135.RELTN_TYPE = 'HOS'
    AND T22.CFD_FLAG = 'Y';

There are several JOIN types (LEFT/RIGHT/INNER/OUTER), so see your using the one you need.