[Q] Make two queries "union" together

From: Brian Lavender <blavender_at_spk.usace.army.mil>
Date: Fri, 15 Jan 1999 18:36:34 GMT
Message-ID: <77o1rh$mh2$1@nnrp1.dejanews.com>

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)

brian

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 - 12:36:34 CST