Can I include the table name in the result of an SQL query?

Question

If I have two tables:

Actor:
ID | Name
1  : Patrick
3  : Emma
7  : Vanessa

Singer:
ID | Name
4  : Will
5  : Madonna
13 : Emma

Can I generate the following table from an SQL query, which contains the table name each record came from?

ID | Career | Name
1  : Actor  : Patrick
3  : Actor  : Emma
4  : Singer : Will
5  : Singer : Madonna
7  : Actor  : Emma
13 : Singer : Emma

I'm assuming the ID column items are unique across the two tables, but not the names.

Microreader

Bad DB design or bad sample. The same person can be an actor and a singer. – Rasheedarasher

Tell that to the people on broadway – Mailbox

It would appear that no-one has actually answered the question though. None of the responses use the table name, preferring instead to use constants that happen to be the same. – Microreader

Answer 1

select ID, 'Actor' as Career, Name from Actor
union all
select ID, 'Singer' as Career, Name from Singer

Or something along these lines.

Acceptable

I'm accepting this answer, but I note that it doesn't actually answer the question. The table name does not appear in the result, rather there is a constant that happens to be the same. I'm assuming my actual question can't be answered in SQL. – Microreader

Answer 2

SELECT ID, 'Actor' AS Career, Name FROM Actor
UNION
SELECT ID, 'Singer' AS Career, Name FROM Singer

Mailbox

It's a nitpick, but you should use UNION ALL if you don't need duplicates removed. – Balch

It's not a nitpick. You are exactly right. If there were a unique constraint on (Id, Name) UNION ALL would effectively yield the same result as UNION, but only faster. But honestly I wouldn't care for duplicate records in this case. – Mailbox

Answer 3

OK, two people beat me to the sample query I was posting.

But, I think the better answer to your root question is, if "Career" is a relevant attribute in your data, and the IDs are expected to be unique, why not have one table in which Career is an actual column?

Balch

+1 While others, as me (already deleted), were answered as asked, this one is the best answer, actually. – Rasheedarasher

Normalization is for suckers :p This is the eternal tradeoff between answering the question, or solving his problem :) – Mailbox

It's part of a larger problem, reduced for the purposes of asking a question on SO. I'm trying to figure out a minimally disruptive way of adding a minor feature. – Microreader

Answer 4

Since you know the table names you're querying from you can simply include the table name as a literal value in the result set:

SELECT ID, 'Actor', Name FROM Actor
  UNION SELECT ID, 'Singer', Name FROM Singer;

Moya

Answer 5

Try:

select id, 'Actor' as Career, Name
from Actor
union
select id, 'Singer' as Career, Name
from Singer

Twophase

Answer 6

This is not ideal either but it does answer your question assuming the database has only two tables.

DECLARE @t1 nvarchar(10) = (SELECT top 1 TABLE_NAME
                            FROM INFORMATION_SCHEMA.TABLES)
DECLARE @t2 nvarchar(10) = (SELECT top 1 TABLE_NAME
                            FROM INFORMATION_SCHEMA.TABLES
                            WHERE NOT TABLE_NAME = @t1)

DECLARE @SQLcommand nvarchar(400)

SET @SQLcommand = 'select ID, '+ @t1 +' as Career, Name from ' + @t1 +
                  ' union all select ID, '+ @t2 +' as Career, Name from ' +@t2

EXEC(@SQLcommand)

I agree with above that the design really does need to get looked at for you to need this.

Marjorie

Answer 7

You need a Careers table:

ID Career

1 Actor

2 Singer

3 Musician

Add Career ID to the Singer and Actor tables dynamically

select * from ( Select *,'1'CareerID from Actor union Select *,'2'CareerID from Singer union Select *,'2'CareerID from Singer ) Talent JOIN Careers on Careers.ID = Talent.CareerID

Psychopathology