mysql views

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_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

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

Try this

   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 ALL
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

I'm not sure, I tried that with a table on my server and it worked.. I guess it will work for you. Hint the only difference here is that I used "union all" instead of just "union"