May 26, 2003 at 1:30 am
Hi,
Due to bad DB design (at least I think so) we have a table in our database (A) of which some rows refer to one table (B) and the rest refer to a different table (C)
Now I want to select a list of rows from table A of both types based on criteria in the refered tables (B and C), this wouldn't be a problem if I wanted only values from A but I actually want a JOIN, and since there is no join between B and C, I get a cartesian.
Is there any way to take two different SELECTS (that output the same columns) and join them to a single table?
May 26, 2003 at 3:14 am
Use UNION with two normal joins.
SELECT A.*, B.*, null, ...
FROM A INNER JOIN B
ON A.ID_FIELD = B.ID_FIELD
UNION ALL
SELECT A.* , ... , C.*
FROM A INNER JOIN C
ON A.ID_FIELD = C.ID_FIELD
You have to make sure that both queries in the UNION return the same fields. So, if the layout of table B and C is different, you have to 'pad' the resultset with enough fields to make up for the difference. That is why I added the '...' in the code.
May 26, 2003 at 3:39 am
Thanks so much! it's exactly what I needed (although without the "ALL" - to not have duplicates)
It's those usefull T-SQL stuff that are so elusive sometimes.
I should really slap myself for:
a. not knowing this operator before
b. for not reading the SELECT specification carefully enough
I was already thinking to a level of having to create a Temporary Table and Inserting values into it...
Viewing 3 posts - 1 through 2 (of 2 total)
You must be logged in to reply to this topic. Login to reply