CODEWITHSUNDEEP
sql-server
ChangeWorld
ChangeWorld

Reputation: 431

Get the result from 3 tables that have the same name of fields

I have 3 tables in SQL Server:

Table MyParents

ID   Name   Surname

Table MyCousins

ID   Name   Surname

Table MyGrandParents 

ID   Name   Surname

I would like to ask: is there a way to get the "Name" and "Surname" from all the 3 tables and to know from what table they are coming from in one query?

So the result should be

Name         Surname        Table
-----------------------------------------
Giovanni     Rossi          MyParents
Rosanna      Blu            MyCousins
Seth         TheGreat       MyGrandParents

Any help is appreciated

Upvotes: 0

Views: 39

Answers (1)

sticky bit
sticky bit

Reputation: 37472

You can use UNION ALL and a literal for the table.

SELECT name,
       surname,
       'MyParents' [table]
       FROM MyParents
UNION ALL
SELECT name,
       surname,
       'MyCousins' [table]
       FROM MyCousins
UNION ALL
SELECT name,
       surname,
       'MyGrandParents' [table]
       FROM MyGrandParents;

But note, that your design is probably improvable. There should be only one table for all the people and a linking table storing how they are related.

Upvotes: 2

Related Questions