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

From: Brian Lavender <blavender_at_spk.usace.army.mil>
Date: Fri, 15 Jan 1999 19:48:04 GMT
Message-ID: <77o61g$qde$1@nnrp1.dejanews.com>

In article <77o1rh$mh2$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, I know it is probably wierd that I am answering my own question, but shortly after asking it, I figured out part of the answer. I am not all the way there yet, so here goes again. I figured out how to join the two together. This gives me a single answer table where I have the accounts which resided in BOTH tables and the SUM_ATP_AMT amount and the SUM_PAID_AMT amount. Here is the next problem. Some of the accounts exist only in one table (Well I suppose virtual table created by the subquery). THe join exludes them, so I need to get the results of that to include with the join. Below is the join query I came up with to get the first part of the answer. I take it I will create a query that will find the accounts and either the SUM_ATP_AMT amount or the SUM_PAID_AMT amount and union it to the original query. How do I do this?

join query

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

join query results:

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	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
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

>
> query 1:
> -----------------
> 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
>
> results of query 1:
> --------------------
> WI_CODE, COEMIS, SUM_ATP_AMT
> 001V3K NONE 58,663.00
> 001V3L NONE 8,664.00
> 001V3M NONE 8,659.00
> 001V3N NONE 8,664.00
> 001V3P NONE 8,669.00
> 001V3Q NONE 8,659.00
> 001V3S NONE 8,669.00
> 001V3T NONE 8,659.00
> 001V3V NONE 8,664.00
> 001V3W NONE 8,668.00
> NONE PL4026 6207 C2004 9,999.00
> NONE QX722B C050 27,041.00
> NONE RP7675 C202 9,850.00
> NONE RP7675 C203 9,850.00
> U05551 PL5025 5946 T0300 8,888.00
> U09159 RP7675 C200 15,938.00
> U09162 RP7675 C204 9,850.00
> U09163 RP7675 C205 9,850.00
> U09164 RP7675 C206 9,850.00
> U09165 RP7675 C207 9,877.00
>
> query 2:
> -----------------
> 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
>
> results of query 2:
> ---------------------
> WI_CODE, COEMIS, SUM_PAID_AMT
> 001V3K NONE 9,663.00
> 001V3L NONE 8,664.00
> 001V3M NONE 10,659.00
> 001V3N NONE 8,664.00
> 001V3P NONE 10,081.00
> 001V3Q NONE 8,659.00
> 001V3S NONE 8,640.10
> 001V3T NONE 8,659.00
> 001V3V NONE 8,664.00
> 001V3W NONE 8,668.00
> NONE QX722B C050 27,041.00
> NONE RP7675 C202 9,850.00
> NONE RP7675 C203 9,850.00
> U09159 RP7675 C200 15,938.00
> U09162 RP7675 C204 9,850.00
> U09163 RP7675 C205 9,850.00
> U09164 RP7675 C206 9,850.00
> U09165 RP7675 C207 9,877.00
>
> I want this:
> ------------------
> 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
>

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 - 13:48:04 CST