CODEWITHSUNDEEP
mysqlunion
undone
undone

Reputation: 7888

mysql union doesn't work as expected

I had a mysql query :

Select count(*) as cnt from  page06 where dl = 1 and TYPE = 'mempage' union 
Select count(*) as cnt from  page06 where err = 1 and TYPE = 'mempage' union  
Select count(*) as cnt from  page06 where dl = 1 and err=1 and TYPE = 'mempage'

In most cases dl and err columns are 0 so two last select will return 0. I found out this query returns only one that is not what i expected. I found this solution:

Select count(*) as cnt,'s' as p from  page06 where dl = 1 and TYPE = 'mempage' union 
Select count(*),'d' as p  from  page06 where err = 1 and TYPE = 'mempage' union  
Select count(*),'f' as p   from  page06 where dl = 1 and err=1 and TYPE = 'mempage'

This query works perfectly but I have written codes in first format. I want to know why this happens and is there any solution (Except what I said) to solve this problem?
The reason I don't want to use my solution is I have lots of written queries that I don't want to (or even cannot) change them! what I'm seeking is some king of options to set to connection (like set names 'latin1';)

Upvotes: 1

Views: 494

Answers (1)

Chandu
Chandu

Reputation: 82943

Use UNION ALL instead of UNION

Check this post for a detailed explanation on the difference : What is the difference between UNION and UNION ALL?

SELECT count(*) AS cnt FROM  page06 WHERE dl = 1 AND TYPE = 'mempage' UNION ALL
SELECT count(*) AS cnt FROM  page06 WHERE err = 1 AND TYPE = 'mempage' UNION ALL
SELECT count(*) AS cnt FROM  page06 WHERE dl = 1 AND err=1 AND TYPE = 'mempage'

Upvotes: 3

Related Questions