CODEWITHSUNDEEP
mysqlsqlselectunion
Haradzieniec
Haradzieniec

Reputation: 9340

Select with UNION ALL to display tablename as an additional column

For MSSQL or MySQL, here is a query that unites two results into one:

SELECT boy as person from table1
union all
SELECT girl as person from table2

How to modify the query above so that the result contains the second (added) column with the name of the table (so it contains table1 or table2 value).

Upvotes: 0

Views: 154

Answers (3)

Allan
Allan

Reputation: 17429

You will need to add them as literals:

SELECT boy as person, 'table1' as tablename from table1
UNION ALL
SELECT girl as person, 'table2' from table2

Upvotes: 1

Pரதீப்
Pரதீப்

Reputation: 93724

Just Hard code the tablename in Second Column

SELECT boy as person,'Table1' as Tablename from table1
UNION ALL
SELECT girl as person ,'Table2' as Tablename from table2

Upvotes: 3

radar
radar

Reputation: 13425

you can give string lateral with table name as second column

SELECT boy as person, 'table1' as column2 from table1
union all
SELECT girl as person, 'table2' as column2 from table2

Upvotes: 4

Related Questions