MySQL – How to Get Table Name from Union Query

MySQLmysqliperformancequery-performanceunion

This is my query

SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename1 where Active =1 
union 
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename2 where Active =1
union  
SELECT Id, productName, Largeimagepath, Discount, Price, Image FROM tablename3 where Active =1

It is working properly.

Now I want to fetch the table name for the respective productName in future.

So how can I fetch it?

I tried AS.. like this:

SELECT Id, productName, Largeimagepath, Discount, Price, Image
FROM tablename3 AS tablename
where Active = 1;

but didn't get output.

How shall I correct the query and also Improve the performance of query?

Best Answer

Typically when using UNION and you need to know what table a specific row comes from, you'd use a hard-coded value stored in a column similar to:

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source
FROM tablename1 
where Active =1 
union 
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as Source
FROM tablename2 
where Active =1
union  
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as Source
FROM tablename3 
where Active =1;

This will return a new column called Source with an identifier that shows which table the row came from.

As @ypercube suggested in the comments you may also want to consider altering this to a UNION ALL - this will include duplicate but you'll also have an identifier on which table it came from. Using a UNION ALL will eliminate the performance impact of removing dups.

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as Source
FROM tablename1 
where Active =1 
union all
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as Source
FROM tablename2 
where Active =1
union all
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as Source
FROM tablename3 
where Active =1;

Related Solutions

Mysql – Get wall_post and profile data for several users

Here is your original query

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid) 
    AND (default_wall_posts.userid = 2 OR default_wall_posts.userid = 3)
WHERE
    default_wall_posts.userid = 1 AND
    (
        default_wall_posts.userid = 2 AND
        default_wall_posts.userid = 3
    )
ORDER BY
    default_wall_posts.date_created DESC;

The WHERE is impossible to fulfill because default_wall_posts.userid not be 1 and 2 and 3 at the same time.

Try these changes

SELECT DISTINCT
    default_profiles.*,default_wall_posts.*,
    UNIX_TIMESTAMP( ) - default_wall_posts.date_created AS pass_time
FROM
    (default_wall_posts)
    LEFT JOIN default_profiles
    ON (default_profiles.user_id = default_wall_posts.userid) 
WHERE
    default_wall_posts.userid IN (1,2,3)
ORDER BY
    default_wall_posts.date_created DESC;

MySql: #1221 – Incorrect usage of UNION and ORDER BY

There are several ways to pass the IN / LIMIT limitation and the UNION / ORDER BY syntax requirements.

Wrap the Union in a derived table:

SELECT * FROM comment AS tbl WHERE sscm_id IN
  ( SELECT sscm_id
    FROM     
      ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
          ORDER BY t1.sscm_id DESC LIMIT 3)
      UNION  ALL
        ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
          ORDER BY t2.sscm_id DESC LIMIT 3)
      ) AS tmp 
  ) ;

Use JOIN instead of WHERE / IN:
UNION is required here instead of UNION ALL to avoid duplicates

SELECT tbl.* 
FROM comment AS tbl 
    JOIN 
        ( ( SELECT sscm_id FROM comment AS t1 WHERE t1.sscm_oid = 4 
            ORDER BY t1.sscm_id DESC LIMIT 3)
        UNION 
          ( SELECT sscm_id FROM comment AS t2 WHERE t2.sscm_oid = 3 
            ORDER BY t2.sscm_id DESC LIMIT 3)
        ) AS tmp 
      ON tmp.sscm_id = tbl.sscm_id ;

Best Answer

Related Solutions

Mysql – Get wall_post and profile data for several users

MySql: #1221 – Incorrect usage of UNION and ORDER BY

Related Question