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'
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