CODEWITHSUNDEEP
sql
Arshpreet Wadehra
Arshpreet Wadehra

Reputation: 1023

How to return source table name when using a UNION?

If I use a UNION to select columns from 5 tables, then select a particular value from the results, how can I determine which table that value came from?

Upvotes: 2

Views: 5178

Answers (4)

darkage
darkage

Reputation: 857

select col 1, col 2, 'users' as table_name from users
union all
select col 1, col 2, 'vendors' as table_name from vendors;

'table_name' will contains the source names.

Hope this works

Upvotes: 0

Nick
Nick

Reputation: 169

SELECT columnA, columnB, 'Table 1' FROM table1
UNION ALL
SELECT columnA, columnB, 'Table 2' FROM table2

This will add a column which displays which table the data comes from. Works in Oracle atleast...

Upvotes: 0

Andriy M
Andriy M

Reputation: 77717

Supply table names in a column along other columns you are pulling:

SELECT
  'table1' AS tablename,
  other columns
FROM table1

UNION ALL

SELECT
  'table2' AS tablename,
  other columns
FROM table2

UNION ALL

…

Upvotes: 10

vlad
vlad

Reputation: 4778

add a column to the result list that says which table the data is coming from. e.g.:

select 'table1' tableName,
       columnA,
       columnB
from table1
union
select 'table2' tableName,
       columnC,
       columnD

Upvotes: 5

Related Questions