MySql : UNION is not getting executed when executed as a view

Question

I am trying to create a view for a UNION of 2 select statements that I have created.

The UNION is working fine when executed individually

But the problem is only the 1st part of the UNION is getting executed when I am executing it as a view.

The query I am using is as below

SELECT DISTINCT products.pid AS id, 
                products.pname AS name, 
                products.p_desc AS description,
                products.p_uid AS userid,
                products.p_loc AS location,
                products.isaproduct AS whatisit 
           FROM products
UNION

SELECT DISTINCT services.s_id AS id, 
                services.s_name AS name, 
                services.s_desc AS description,
                services.s_uid AS userid,
                services.s_location AS location,
                services.isaservice AS whatisit 
           FROM services
          WHERE services.s_name

The above works fine when i execute it separately. But when I use it as a view, it does not give me the results of the services part.

Could someone please help me with this?

duplicate : http://stackoverflow.com/questions/514817/creating-mysql-view-using-union/514823#514823 — Learning, Feb 06 '09 at 03:00
I have a similar problem. If I delete one row from `products` table, it's remaining SELECTing on the view, but not when executing the UNION alone... :S — Marçal Juan, Apr 04 '12 at 08:44
Also if I add one row in one table, it doesn't appear on the view! — Marçal Juan, Apr 04 '12 at 09:03
Possible duplicate of [Creating MySQL View using UNION](https://stackoverflow.com/questions/514817/creating-mysql-view-using-union) — sticky bit, Apr 06 '19 at 01:54

score 0 · Answer 1 · answered Nov 13 '10 at 16:59

If you could give the result set for each individual query above and then also give the result set for the UNION query, we could probably provide a better answer to your question. My gut reaction is that the second query may be returning a duplicate value, and since you are using UNION, duplicates are being removed. If you used UNION ALL, then all duplicate rows would be returned. For example, if the first query returned the row:

1 name1 description1 10 Home Y
2 name2 description2 20 Work Y

and the second row returned:

1 name1 description1 10 Home Y

The resulting output would be:

1 name1 description1 10 Home Y
2 name2 description2 20 Work Y

If you want all of the rows returned:

1 name1 description1 10 Home Y
2 name2 description2 20 Work Y
1 name1 description1 10 Home Y

Then you would use a UNION ALL instead of a UNION statement.

score -1 · Answer 2 · answered Feb 06 '09 at 02:15

-1

i think your fields userid and location are swapped in the two selects from the union, if of diferent data types, you will get an error, if not, you will get wrong results...is that it?

answered Feb 06 '09 at 02:15

caiokf

344
6
14

That is how i was using but i have made a mistake while entering here.. Still not working – Feb 06 '09 at 02:19

MySql : UNION is not getting executed when executed as a view

2 Answers2