Re: [Q] Make two queries "union" together

From: Brian Lavender <blavender_at_spk.usace.army.mil>
Date: Fri, 15 Jan 1999 21:40:47 GMT
Message-ID: <77ockt$h0$1@nnrp1.dejanews.com>

In article <77o61g$qde$1_at_nnrp1.dejanews.com>,   I, Brian Lavender, <blavender_at_spk.usace.army.mil> wrote:

> > I have two queries which I want to "union" or "join" the results together. I
> > can't quite figure out how to get a results table which shows that. Here are
> > my two separate queries along with results. How do I bring these two into
> > one? (Sample queries and results below)

Ok, Ok, I figured it out. I created three sub queries which I unioned together to produce the results. Here is an attempt at a plain enlish explanation at what I did. I had a table which showed the equivalent of deposits to an account. Then I had a table to show payments from the accounts. Of course a join will get only those accounts that exist in both the deposit equivalent table (SUM_ATP_AMT) and the payment equivalent table (SUM_PAID_AMT). In order to get those exluded by the join, I created two other sub queries that used the NOT EXISTS. If you look at my results and the initial tables in the first post, (see the thread) you will notice I added an account that had no SUM_ATP_AMT, but does have a SUM_PAID_AMT in order to demonstrate this thing all works. In the end I unioned all the results together. If you know a better way to do this, please let me know.

brian

final query

---

select WI_CODE, COEMIS, SUM_ATP_AMT, SUM_PAID_AMT from (

   select a.WI_CODE, a.COEMIS, a.SUM_ATP_AMT, b.SUM_PAID_AMT from    (

      SELECT WI_CODE, COEMIS, SUM( ATP_AMT ) SUM_ATP_AMT
      FROM ATP_LOG_DETAIL Atp_log_detail
      WHERE   (OBLI_NO = 'DACW05-96-D-0011')
         AND  (DELIVERY_ORDER_NO = '0002')
         AND  (WAD = '0001')
         AND  (WORK_ORDER = 'ENGR')
      GROUP BY WI_CODE, COEMIS

   ) a,

   (

      SELECT WI_CODE, COEMIS, SUM(AMT) SUM_PAID_AMT
      FROM
      (
      SELECT WI_CODE, COEMIS, INIT_AMT AMT
      FROM INITIAL_PAY_DETAIL Initial_pay_detail
      WHERE   (OBLI_NO = 'DACW05-96-D-0011')
         AND  (DELIVERY_ORDER_NO = '0002')
         AND  (WAD = '0001')
         AND  (WORK_ORDER = 'ENGR')

      UNION

      SELECT WI_CODE, COEMIS, AMT_PAY AMT
      FROM PAY_ITEM_DETAIL Pay_item_detail
      WHERE   (DELIVERY_ORDER_NO = '0002')
         AND  (OBLI_NO = 'DACW05-96-D-0011')
         AND  (WAD = '0001')
         AND  (WORK_ORDER = 'ENGR')
      )
      GROUP BY WI_CODE, COEMIS

   ) b
   where a.WI_CODE = b.WI_CODE
   AND a.COEMIS = b.COEMIS
) UNION (
   SELECT WI_CODE, COEMIS, SUM( ATP_AMT ) SUM_ATP_AMT, 0.00 AS SUM_PAID_AMT    FROM ATP_LOG_DETAIL first
   WHERE NOT EXISTS (
   select * from (

         SELECT WI_CODE, COEMIS
         FROM
         (
            SELECT WI_CODE, COEMIS
            FROM INITIAL_PAY_DETAIL Initial_pay_detail
            WHERE   (OBLI_NO = 'DACW05-96-D-0011')
               AND  (DELIVERY_ORDER_NO = '0002')
               AND  (WAD = '0001')
               AND  (WORK_ORDER = 'ENGR')

         UNION

            SELECT WI_CODE, COEMIS
            FROM PAY_ITEM_DETAIL Pay_item_detail
            WHERE   (DELIVERY_ORDER_NO = '0002')
               AND  (OBLI_NO = 'DACW05-96-D-0011')
               AND  (WAD = '0001')
               AND  (WORK_ORDER = 'ENGR')
         )

         ) secondfoo
      where first.COEMIS = secondfoo.COEMIS
      and first.WI_CODE = secondfoo.WI_CODE

      )

      AND  (OBLI_NO = 'DACW05-96-D-0011')
      AND  (DELIVERY_ORDER_NO = '0002')
      AND  (WAD = '0001')
      AND  (WORK_ORDER = 'ENGR')

   GROUP BY WI_CODE, COEMIS
) UNION (
   SELECT WI_CODE, COEMIS, 0.00 AS SUM_ATP_AMT, SUM(AMT) SUM_PAID_AMT    FROM
   (

      SELECT WI_CODE, COEMIS, INIT_AMT AMT
      FROM INITIAL_PAY_DETAIL Initial_pay_detail
      WHERE   (OBLI_NO = 'DACW05-96-D-0011')
         AND  (DELIVERY_ORDER_NO = '0002')
         AND  (WAD = '0001')
         AND  (WORK_ORDER = 'ENGR')

      UNION

      SELECT WI_CODE, COEMIS, AMT_PAY AMT
      FROM PAY_ITEM_DETAIL Pay_item_detail
      WHERE   (DELIVERY_ORDER_NO = '0002')
         AND  (OBLI_NO = 'DACW05-96-D-0011')
         AND  (WAD = '0001')
         AND  (WORK_ORDER = 'ENGR')

   ) first
   where not exists (

      select * from (
         SELECT WI_CODE, COEMIS
         FROM ATP_LOG_DETAIL Atp_log_detail
         WHERE   (OBLI_NO = 'DACW05-96-D-0011')
            AND  (DELIVERY_ORDER_NO = '0002')
            AND  (WAD = '0001')
            AND  (WORK_ORDER = 'ENGR')
      ) secondfoo
      where first.COEMIS = secondfoo.COEMIS
      and first.WI_CODE = secondfoo.WI_CODE

   )
   GROUP BY WI_CODE, COEMIS
)

final results table

---

WI_CODE, COEMIS, SUM_ATP_AMT, SUM_PAID_AMT

001V3K	NONE	58,663.00	9,663.00
001V3L	NONE	8,664.00	8,664.00
001V3M	NONE	8,659.00	10,659.00
001V3N	NONE	8,664.00	8,664.00
001V3P	NONE	8,669.00	10,081.00
001V3Q	NONE	8,659.00	8,659.00
001V3S	NONE	8,669.00	8,640.10
001V3T	NONE	8,659.00	8,659.00
001V3V	NONE	8,664.00	8,664.00
001V3W	NONE	8,668.00	8,668.00
NONE	PL4026 6207 C2004	9,999.00	0.00
NONE	QX722B C050	27,041.00	27,041.00
NONE	RP7675 C202	9,850.00	9,850.00
NONE	RP7675 C203	9,850.00	9,850.00
U05551	PL5025 5946 T0300	8,888.00	0.00
U05663	PL6216 T605	0.00	7,777.00
U09159	RP7675 C200	15,938.00	15,938.00
U09162	RP7675 C204	9,850.00	9,850.00
U09163	RP7675 C205	9,850.00	9,850.00
U09164	RP7675 C206	9,850.00	9,850.00
U09165	RP7675 C207	9,877.00	9,877.00

--------------------

Brian E. Lavender
US Army Corps of Engineers -- Programmer / Systems Analyst Sacramento, CA (916) 557-6623

-----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own Received on Fri Jan 15 1999 - 15:40:47 CST