View over multiple tables containing same columns

Question

I have four tables containing exactly the same columns, and want to create a view over all four so I can query them together.

Is this possible?

(for tedious reasons I cannot/am not permitted to combine them, which would make this irrelevant!)

Answer 1

It is difficult to tell from your query whether you expect the data to be returned based on a UNION, or as a view containing the columns discretely. This obviously has an effect.

Consider the following sample:

TableA
ID  Name   RelatedID
1   John   2
2   Paul   1

TableB
ID  Name   RelatedID
1   Ringo  1
2   George 1

TableC
ID  Name  RelatedID
1   Bob   1

TableD
ID  Name  RelatedID
1   Kate  NULL

Now, run the following query against this:

SELECT ID, Name FROM TableA UNION ALL SELECT ID, Name FROM TableB UNION ALL SELECT ID, Name FROM TableC UNION ALL SELECT ID, Name FROM TableD

This results in the following output:

1 John
2 Paul
1 Ringo
2 George
1 Bob
1 Kate

Is this what you are after? If so, you use a UNION query.

Now, if the effect you are after is to have a discrete view of related data, you may need to do something like this:

SELECT A.ID MasterID, A.Name MasterName, 
       B.ID BandID, B.Name BandName, 
       C.ID BlackadderID, C.Name BlackadderName
       D.ID BlackadderRealID, D.Name BlackadderRealName
FROM
  TableA A
INNER JOIN
  TableB B
ON
  A.RelatedID = B.ID
INNER JOIN
  TableC C
ON
  B.RelatedID = C.ID
INNER JOIN
  TableD D
ON
  C.RelatedID = D.ID

This will result in the following view of the data:

MasterID  MasterName  BandID  BandName BlackAdderID BlackAdderName  BlackadderRealID  BlackadderRealName
1         John        2       George   1            Bob             1                 Kate
2         Paul        1       Ringo    1            Bob             1                 Kate