MySQL Union function

[ceiral]

Hello everyone, I was wondering if somebody would kindly give their opinion to sort the problem I'm facing right now. Supposedly I have two queries; Query 1

SELECT product,customer,SUM(qty)
FROM tblproduct
WHERE trans_date=20090303
AND trans_time>='19:00:00'
AND trans_time<='23:59:59'
AND from_oper=1100
GROUP BY product

Query 2

SELECT product,customer,SUM(qty)
FROM tblproduct
WHERE trans_date=20090304
AND trans_time>='00:00:00'
AND trans_time<='06:59:59'
AND from_oper=1100
GROUP BY product

In which the results is as follows: Query 1 Result

product customer SUM(qty)
A John 20
A Adam 3
B Jenny 15
C John 9
D Abu 8

Query 2 Result

product customer SUM(qty)
A John 5
D Ali 12
D Abu 3

I want the two queries to work as a single query so I joined both of them using UNION like the following:

(SELECT wlth_prod_new,customer,SUM(from_qty)
FROM tblusm_wiplth
WHERE trans_date=20090303
AND trans_time>='19:00:00'
AND trans_time<='23:59:59'
AND from_oper=1100
GROUP BY wlth_prod_new)
UNION
(SELECT wlth_prod_new,customer,SUM(qty)
FROM tblusm_wiplth
WHERE trans_date=20090304
AND trans_time>='00:00:00'
AND trans_time<='06:59:59'
AND from_oper=1100
GROUP BY wlth_prod_new)

and the result would be a union of the results from the first two queries mentioned.

product customer SUM(qty)
A John 20
A Adam 3
B Jenny 15
C John 9
D Abu 8
A John 5
D Ali 12
D Abu 3

By looking from the query result above, I seems that there is a redundant data for product A purchased by John (bold) and product D purchased by Ali. I wanted the query would only return the sums of qty for product X that is purchased by customer Y. In which to my imagination the result should be like this:

product customer SUM(qty)
A John 25
A Adam 3
B Jenny 15
C John 9
D Abu 11
D Ali 12

Can somebody give

[Luc Pattyn]

Hi, You only want one result set, so you should perform only one query. You know the AND operator, what you also need is the OR operator and some parentheses. Try something along these lines:

... WHERE (field1=val1 AND field2=val2) OR (field1=val3 AND field2=val4) ...

:)

Luc Pattyn [Forum Guidelines] [My Articles]

---

The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Show formatted code inside PRE tags, and give clear symptoms when describing a problem.

---