Joining Two Queries into one table

  • 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?

  • 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.

  • 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