Get table name from union query?
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;
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;