Select the table name as a column in a UNION select query in MySQL

Question

I want to be able to select the name of a the table a row is from as a column in a union select query. Something like this:

SELECT [TABLENAME], text from table1
UNION
SELECT [TABLENAME], text from table2
ORDER BY date

Does anyone know if this is possible? Thanks

Answer 1

You are already querying on that table itself. Eg:- table1 and table2

So you can basically output table name as string itself -

SELECT 'table1' as tableName, text from table1
UNION
SELECT 'table2' as tableName, text from table2
ORDER BY date

Answer 2

given that you've got to type the table name into the sql anyway, why not just include it as a string in the select too? i.e.

SELECT 'table1' as tablename, text from table1
UNION
SELECT 'table2' as tablename, text from table2
ORDER BY date