The Tomes Of Experience

Sounds weird, right? Not quite :)

Imagine if you will, Xerxes sitting at his work desk on a
Saturday LATE afternoon
really pushing to get his work done to meet a looming deadline. At this point
in the story, my current task is to create an SQL query to load the same
information from three separate tables, but return the data as one column in
the result set. (See picture below…

face=Arial>

Okay so we have three tables like in the diagram
above….Tables B and C have a foreign key back to Table A. What I needed
to do was grab a list of ALL *_Name fields in each table and have them returned
in one column (because this “Name” field became one of the columns
in an outer query – ie: this also had to be a subquery.

At first it looks like a simple INNER JOIN on A_FK –
A_PK but if you do that then you’ll still end up getting 3 columns in the
SELECT clause…

Funnily enough the answer is so simple, but eluded me at the
start….Simple set theory dictates that the result of a UNION operation on
any two sets is the conglomeration of those two sets excluding
duplicates….That means I just had to UNION 3 select statements together,
and viola, all values for that “name” across each table came into
one field…(with a bit of field aliasing and inline table’ing :P)

For those interested – here is what the SQL looked
like (for obvious purposes, I cant post the real code, but hopefully this
conveys the point)

SELECT

           
A.PK,

           
NAMES.Name

           
D.Field,

           
E.Field,

           
F.Field,

           
G.Field,

FROM

           
A JOIN

           
(

                       
SELECT

face=Arial>A2.A_PK color=blue>AS PK,

face=Arial>A2.A_NAME color=blue>AS Name

                       
FROM A A2

                       
UNION color=blue>

                       
SELECT

face=Arial>B.B_PK color=blue>AS PK,

face=Arial>B.B_NAME color=blue>AS Name

                       
FROM B

                       
UNION color=blue>

                       
SELECT

face=Arial>C.C_PK color=blue>AS PK,

face=Arial>C.C_NAME color=blue>AS Name

                       
FROM C

           
) AS NAMES color=blue>ON NAMES.PK = A.PK

           
JOIN D color=blue>ON D.D_PK = A.A_PK

face=Arial>JOIN size=2 face=Arial> w:st="on">E ON
E.E_PK = A.A_PK

face=Arial>JOIN size=2 face=Arial> F color=blue>ON F.F_PK = A.A_PK

face=Arial>JOIN size=2 face=Arial> G color=blue>ON G.G_PK = A.A_PK

WHERE

           
Blah….