Include table name in SQL query
What I need to do is get a table name in result from a union query with 4 selects from different tables. I need to get the id and the table name for further processing.
For example i have table1, table2, table3 table4 and have a query:
SELECT id from table1,blablabla
UNION
SELECT id from table2,blablabla
UNION
SELECT id from table3,blablabla
UNION
SELECT id from table4,blablabla
And I need the r开发者_运维问答esult like:
1, blablabla, table1
4, blablabla, table4
7, blablabla, table2
SELECT
ID,
'table1' as TableName
FROM
table1
UNION
...
SELECT
ID,
'table4' as TableName
FROM
table4
SELECT myid, My otherfield, 'table1' as tablename From table1
UNION
SELECT myid, My otherfield, 'mytabl2' From table2
Consider a couple of other points, if these are tables that are mutually exclusive use UNION ALL Instead, it will be much much faster. Also, this type of question often comes up when the database design is incorrect, if the same fields are in these tables, why are they not all in one table? Redesign if possible, at least consider if you would be better served doing this. Not knowing what that tables are, I can;t evelauate if they should be in one table, but usually this is a code smell.
Add the table name in your request
SELECT
id,
blablabla,
'table1' as tableName
FROM
table1
UNION
SELECT
id,
blablabla,
'table2' as tableName
FROM
table2
...
精彩评论