Junaid Ali Junaid Ali - 1 month ago 5
SQL Question

How to get rows from one or another joined table and then further to more joined tables depending on which first two tables were joined

I have three tables (a, b, c) and two (b and c) need to be joined to get detail data from the first table a. But the problem is that I need to do this in one query.

If I join both tables in the same query than no records are found as detail data is either in b or c, but never in both.

To further complicate things, I need to further join other tables (b2, c2) based on with the record found is from b or c.

I am using MS SQL.

The query I have now is:

select a.*, b.name1, c.name1, b2.url, c2.url
left join b on a.aID = b.aID
left join c on a.aID = c.aID
inner join b2 on b.bID = b2.bID
inner join c2 on c.cID = c2.cID
where a.date > '9/1/2016'


I searched for a few days, but no one seems to need to go after the fourth and fifth tables in the query and so couldn't find any similar answer

Is there any way to do this? Performance is not an issue as the number of records will be less than 1,000 after executing a where clause that will always limit the records from table a.

Answer

A series of left joins with inner joins as subqueries should do the trick. The subqueries pull the b/b2 and c/c2 data together for reference in the left joins with a:

select a.*, b.name1, c.name1, b2join.url, c2join.url
FROM a
LEFT JOIN b
    on a.aID = b.aID
LEFT JOIN c
    on a.aID = c.aID
LEFT JOIN 
        (SELECT b.aID, b.bID, b2.url
            FROM b
        INNER JOIN b2
            on b.bID = b2.bID) as b2join
    on b2join.aID = a.aID
LEFT JOIN 
        (SELECT c.aID, c.cID, c2.url
            FROM c
        INNER JOIN c2
            on c.cID = c2.cID) as c2join
    on c2join.aID = a.aID
Comments