CODEWITHSUNDEEP
sql
John McAleely
John McAleely

Reputation: 1939

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

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.

Upvotes: 5

Views: 25767

Answers (7)

user27306644
user27306644

Reputation: 1

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

Upvotes: -1

John Fraser
John Fraser

Reputation: 58

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.

Upvotes: 0

Dave Costa
Dave Costa

Reputation: 48111

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?

Upvotes: 4

Matt Lacey
Matt Lacey

Reputation: 65564

Try:

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

Upvotes: 2

Larry Lustig
Larry Lustig

Reputation: 50970

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;

Upvotes: 2

Yannick Motton
Yannick Motton

Reputation: 35971

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

Upvotes: 4

Anton Gogolev
Anton Gogolev

Reputation: 115741

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

Or something along these lines.

Upvotes: 17

Related Questions