How to use Union in two query/Other Way

[ratzp]

Query1

select BD.PAYMENT_CODE,PM.PAYMENT_DESC as 'PAYMENTMODE', BD.COUPONTYPE, SUM(AMOUNT_RECEIVED) as TOTAL, 0 as total1
from BOM_HEADER BH JOIN BOM_DETAIL BD ON
BH.SHOPCODE=BD.SHOPCODE AND BH.CASHINGUP_ID=BD.CASHINGUP_ID 
join PAYMENT_MODE_MASTER PM ON BD.PAYMENT_CODE=PM.PAYMENT_CODE
where BH.CASHINGUP_ID=1
GROUP BY BD.PAYMENT_CODE,BD.COUPONTYPE,PM.PAYMENT_DESC

PAYMENT_CODE PAYMENTMODE COUPONTYPE TOTAL total1
------------ ------------------------------ -------------------- ---------------------------------------- -----------
CA CASH NULL 1500.00 0
HP HIRE PURCHASE NULL 1200.00 0
CU COUPON MAX 100.00 0
CU COUPON NEWS PAPER CUTTING 300.00 0

(4 row(s) affected)



Query 2

select  BP.PAYMENT_CODE as CODE, PM.PAYMENT_DESC as 'PAYMENTMODE',CH.COUPON_TYPE,
0 as total,
SUM(BP.AMT) AS TOTAL1
from BILL_HEADER BH join BILL_PAYMENT BP 
on BH.SHOPCODE=BH.SHOPCODE AND BH.TILL_NUMBER=BP.TILL_NUMBER AND BH.BILL_NUMBER=BP.BILL_NUMBER AND BH.BILL_DATE=BP.BILL_DATE
join PAYMENT_MODE_MASTER PM ON BP.PAYMENT_CODE=PM.PAYMENT_CODE
LEFT OUTER join COUPON_HEADER CH ON BP.COUPON_EXCHANGE_CODE=CH.COUPON_CODE
WHERE BH.BILL_STATUS IN ('B','A') AND BP.ADVANCE_FLAG<>'Y' AND CASHINGUP_ID=1 AND BH.SHOPCODE='11110'
GROUP BY BP.PAYMENT_CODE,CH.COUPON_TYPE,PM.PAYMENT_DESC

CODE PAYMENTMODE COUPON_TYPE total TOTAL1
---- ------------------------------ -------------------- ----------- ----------------------------------------
CA CASH NULL 0 219893.50000
CN CREDIT NOTE NULL 0 2716.00000
HP HIRE PURCHASE NULL 0 12910.00000
CU COUPON News Paper Coupons 0 300.00000
CU COUPON Price Off 0 100.00000
CU COUPON Sodexho 0 380.00000

(6 row(s) affected)



Expected result
CODE PAYMENTMODE COUPON_TYPE total TOTAL1
---- ------------------------------ -------------------- ----------- ----------------------------------------
CA CASH NULL 1500.00 219893.50000
CN CREDIT NOTE NULL 0 2716.00000
HP HIRE PURCHASE NULL 1200.00 12910.00000
CU COUPON News Paper Coupons 0 300.00000
CU COUPON Price Off 0 100.00000
CU COUPON Sodexho 0 380.00000
CU COUPON MAX 100.00 0
CU COUPON NEWS PAPER CUTTING 300.00 0

 

[HarleyQuinn]

Hi,

For using the UNION operator (which I assume is your question?) Make sure that your results returned in both queries have the same structure and datatypes (e.g. you will need to rename the CODE column in your second query). Once you have confirmed that, you just need to add the UNION, e.g.

Select * from Table1...
UNION
Select * from Table2...

Using UNION will remove duplicate rows in the results sets, if you wish to return all rows in the results use UNION ALL instead of UNION.

Hope this helps

HarleyQuinn
---------------------------------
Get the most out of Tek-Tips, read FAQ222-2244 before posting.