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…
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
A2.A_PK AS PK,
A2.A_NAME AS Name
FROM A A2
UNION
SELECT
B.B_PK AS PK,
B.B_NAME AS Name
FROM B
UNION
SELECT
C.C_PK AS PK,
C.C_NAME AS Name
FROM C
) AS NAMES ON NAMES.PK = A.PK
JOIN D ON D.D_PK = A.A_PK
JOIN E ON
E.E_PK = A.A_PK
JOIN F ON F.F_PK = A.A_PK
JOIN G ON G.G_PK = A.A_PK
WHERE
Blah….
