CODEWITHSUNDEEP
sqlsql-serverjoinunion
Steve S
Steve S

Reputation: 219

SQL Combine two tables in a View

Simple Table Diagram

I have three tables (simplified table diagram above). From these three tables I would like to create a view that would be the combination of TableA and TableB (all rows from tableA and all from TableB with no duplicates). The catch is I don't want the foriegn keys from TableA but instead I want the names from TableC in the view. A row would something like below

Id, FullName_A, FullName_B

Is this possible?

Upvotes: 1

Views: 2547

Answers (1)

Linger
Linger

Reputation: 15048

If I am understanding you correctly the following SQL Fiddle example will get you what you need:

SELECT A.Id, C1.FullName AS APerson, C2.FullName As BPerson
FROM TableA AS A
  LEFT JOIN TableC AS C1 ON A.FK_PersonA = C1.Id
  LEFT JOIN TableC AS C2 ON A.FK_PersonB = C2.Id
UNION
SELECT B.Id, B.FullName1 AS APerson, B.FullName2 AS BPerson
FROM TableB AS B

Upvotes: 1

Related Questions