12

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.

  1. 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.

  1. How shall I correct the query and also Improve the performance of query?
Community
  • 1
Prashant Tapase
  • 279
  • 1
  • 2
  • 15

3 Answers3

25

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;
Taryn
  • 9,746
  • 4
  • 48
  • 74
3

You can try this with UNION ALL

as probably you need duplicate entry too as will adding table name it will be unique and performance will be also improved

SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename1' as TableName
FROM tablename1 
where Active =1 
UNION ALL
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename2' as TableName
FROM tablename2 
where Active =1
UNION ALL
SELECT Id, productName, Largeimagepath, Discount, Price, Image, 'Tablename3' as TableName
FROM tablename3 
where Active =1;
vijayp
  • 2,786
  • 1
  • 17
  • 20
0

the below query works better and gives you better idea as above queries don't render rows twice.

SELECT t1.*,t1.table_name
FROM
t1
UNION ALL
SELECT t2.*,t2.table_name
FROM
t2
LEFT OUTER JOIN 
t1
ON t1.id=t2.id
WHERE t1.id=null

the above script don't give duplicate data rows and make sure

  1. All common data bteween t1 and t2 as source t1 table.
  2. Non t2 rows source as t1 table .
  3. Non t1 rows source as t2 table.

if you want common data from t2 table , flip left join

Manoj Kalluri
  • 101
  • 1