MySql Union not getting executed in a view

Question

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_loc AS location,
                products.p_uid AS userid,
                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

Show the view definition

Answer 2

From the union description:

The column names from the first SELECT statement are used as the column names for the results returned. Selected columns listed in corresponding positions of each SELECT statement should have the same data type. (For example, the first column selected by the first statement should have the same type as the first column selected by the other statements.)

This means, that userid and location are mixed up inside the second select statement.