Join two queries side by side

Question

I need help in joining the two queries below. I am new to Oracle. I went through the posts here but the joins mentioned are for simple queries. Please help me in displaying the result of below two queries side by side.

(select Branch, count(*) as "No of Accounts Opened"
      from (SELECT A.CUST_AC_NO,
                   A.CCY,
                   A.branch_code Branch,
                   A.ACY_CURR_BALANCE,
                   A.AC_OPEN_DATE,
                   B.CUSTOMER_NAME1,
                   C.LIMIT_AMOUNT,
                   D.ACCOUNT_CLASS,
                   D.DESCRIPTION
              FROM STTM_CUST_ACCOUNT  A,
                   STTM_CUSTOMER      B,
                   getm_facility      C,
                   STTM_ACCOUNT_CLASS D,
                   getm_liab_cust     e
             WHERE B.CUSTOMER_NO = A.CUST_NO
               AND A.ACCOUNT_CLASS = D.ACCOUNT_CLASS
               and c.liab_id = e.liab_id
               and e.customer_no = b.customer_no
               and e.customer_no = a.cust_no
               AND B.LIABILITY_NO = e.customer_no
               AND RTRIM(C.LINE_CoDe) || LTRIM(TO_CHAR(C.LINE_SERIAL)) =
                   RTRIM(A.LINE_ID)
               AND A.RECORD_STAT = 'O'
               and c.record_stat = 'O'
               and e.record_stat = 'O'
                  --AND to_char(A.AC_OPEN_DATE,'YYYY')=:Year
                  --AND trim(to_char(A.AC_OPEN_DATE,'Month'))=:Month
                  --AND a.BRANCH_CODE ='001'
               AND A.CCY <> 'ZWD'
            UNION
            SELECT A.CUST_AC_NO,
                   A.CCY,
                   Branch_code Branch,
                   A.ACY_CURR_BALANCE,
                   A.AC_OPEN_DATE,
                   B.CUSTOMER_NAME1,
                   NULL LIMIT_AMOUNT,
                   D.ACCOUNT_CLASS,
                   D.DESCRIPTION
              FROM STTM_CUST_ACCOUNT  A,
                   STTM_CUSTOMER      B,
                   STTM_ACCOUNT_CLASS D
             WHERE B.CUSTOMER_NO = A.CUST_NO
               AND A.ACCOUNT_CLASS = D.ACCOUNT_CLASS
               AND A.RECORD_STAT = 'O'
                  --AND to_char(A.AC_OPEN_DATE,'YYYY')=:Year
                  --AND trim(to_char(A.AC_OPEN_DATE,'Month'))=:Month
                  --AND BRANCH_CODE ='001'
               AND A.CCY <> 'ZWD')
     group by Branch
     order by Branch) A

The second query is

(select Branch,count(*) as "No of Accounts Closed"  from(SELECT
    a.branch_code Branch,
    A.CUST_AC_NO,
    A.CCY,
    A.ACY_CURR_BALANCE,
    a.maker_id,
    a.maker_dt_stamp,
    a.checker_id,
    A.CHECKER_DT_STAMP,
    B.CUSTOMER_NAME1,
    C.ACCOUNT_CLASS,
    C.DESCRIPTION
    FROM
    STTMS_CUST_ACCOUNT A,
    STTMS_CUSTOMER B,
    STTMS_ACCOUNT_CLASS C
    WHERE
    B.CUSTOMER_NO = A.CUST_NO
    AND A.ACCOUNT_CLASS = C.ACCOUNT_CLASS
    AND A.RECORD_STAT = 'C'
    ---AND A.BRANCH_CODE ='001'- :brn
    --AND trunc(to_char(A.CHECKER_DT_STAMP,'YYYY'))=:Year
    --AND trim(to_char(A.CHECKER_DT_STAMP,'Month'))=:Month
    ORDER BY
    CUSTOMER_NAME1)group by Branch order by Branch) B

Answer 1

The only common column between these queries is branch so I'm going to assume that you want to the number of accounts closed and opened per branch.

Firstly, these queries have a lot of extraneous information; only select what you need to. Secondly you should really be using explicit joins. They are a lot clearer, remove a obvious point where errors can occur and have been around for a few decades as the SQL standard.

The simple and quick answer to your question would be:

select a.*, b.*
  from query1 a
  full outer join query2 b
    on a.branch = b.branch

I use full outer join as there's no guarantee that the branch in one query exists in the other. I'm not a fan of quick and simple when I can go into detail though....

The best thing to do is to remove your where conditions to a sum(case when...) in your select. This enables you to only scan your tables twice rather than thrice, and then avoid doing a full outer join as you would have to do at the moment.

Please forgive me if I get these joins slightly wrong; I might make a mistake translating them to the explicit join syntax. I've removed some of your where conditions to the join as they should have been there.

As a little side note your worry about your query being "complex" shouldn't be a worry. Essentially, no matter how long your query you can treat it as if it were the simplest possible; just be aware of what data you think should be returned and be ready to investigate if it seems wrong.

select branch
     , sum(opened) as "number of accounts opened"
     , sum(closed) as "number of accounts closed"
   from ( select branch
               , sum(case when a.record_stat = 'C' then 1 
                          else 0 end) as closed
               , sum(case when a.record_stat = 'O' and a.ccy <> 'zwd' then 1 
                          else 0 end ) as opened
            from STTMS_CUST_ACCOUNT A
            join STTMS_CUSTOMER B
              on B.CUSTOMER_NO = A.CUST_NO
            join STTMS_ACCOUNT_CLASS C
              on A.ACCOUNT_CLASS = C.ACCOUNT_CLASS
              -- include where condition to use indexes (if possible)
           where a.record_stat in ('C','O')
           group by branch
              -- union would imply a distinct, which we don-t want.
           union all
          select branch
               , count(*) as opened
               , 0 as closed
            from STTM_CUST_ACCOUNT A
            join STTM_CUSTOMER  B
              on B.CUSTOMER_NO = A.CUST_NO
            join getm_facility C
              on rtrim(C.LINE_CoDe) || ltrim(to_char(C.LINE_SERIAL)) 
                 = rtrim(A.LINE_ID)
              -- moved from where as this is a join condition
             and a.record_stat = c.record_stat
            join STTM_ACCOUNT_CLASS D
              on A.ACCOUNT_CLASS = D.ACCOUNT_CLASS
            join getm_liab_cust e
              on c.liab_id = e.liab_id
             and e.customer_no = b.customer_no
             and e.customer_no = a.cust_no
             and b.liability_no = e.customer_no
              -- moved from where as this is a join condition
             and a.record_stat = e.record_stat
              -- only want one number returned so conditions in the where clause
           where A.RECORD_STAT = 'O'
             and A.CCY <> 'ZWD'
           group by branch
                 )
 group by branch

Sorry, I couldn't be bothered to case everything similarly.

Further Reading:

• Join (SQL) - Wikipedia
• A Visual Explanation of SQL Joins - Coding Horror
• In Function - Tech on the Net

Answer 2

select 
    branches.branch_code,
    first_query."No of Accounts Opened",
    second_query."No of Accounts Closed"
from
   (select distinct branch_code from STTMS_CUST_ACCOUNT) branches
   left outer join (
       -- first query here
   ) first_query on branches.branch_code = first_query.Branch
   left outer join (
       -- second query here
   ) second_query on branches.branch_code = second_query.Branch

Because you haven't given a separate table for branches we have to do the select distinct in the first alias. If you have another table with just this information, it'd be better to use it instead.

I should add that it seems like your queries could probably be simplified (it doesn't look like you use all the fields you fetch).