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

Question

0 vote down star

Hi,

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?

Answer 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?

Answer 2

Do you have a common key to correlate between rows between the two tables?

Your union needs a way to link these two tables together or this will not work

Answer 3

what is the SQL that you used to create the view itself?