Query to return related data via 2 juntion tables for 3 many to many tables.

mumblesnz
mumblesnz used Ask the Experts™
on
I need to create a report which select data that is linked to 3 tables.
Problem is all three tables are many to many and there are junction tables inbetween.

E.g
(Table A - TableA_ID, OtherColumns),  (table B - TableB_ID, OtherColumns),  (TableAjuncionTableB - BaseID, RelatedID)
(Table C - TableC_ID, OtherColumns), (TableBjunctionTableC - BaseID, RelatedID)

I want all the tableA data
Then any data that maybe linked to TableB,
Then any data that maybe linked to TableC via tableB

At moment I can get TableA and TableB data ok, and tableB and tableC data ok. But I want to retreive data in one script efficiently as I will need to retreive data via another 2 or 3 tables deeper if need.

--Table A with all links from B table information
select
"tableA"."Id" as "tableA Id",
"tableA"."Name" as "tableA Name",
"tableA"."Number",
"tableB"."Id" as "tableB ID",
"tableB"."Name" as "tableB Name",
"tableB"."Number"
FROM   ("tableA" INNER JOIN "tableAjunctionTableB" A2B ON "tableA"."Id"=A2B."BaseId")
            INNER JOIN "tableB" ON A2B."RelatedId"="tableB"."Id"
ORDER BY "tableA"."Id"


--TableB with all links from TableC
select
"TableB"."Id" as "TableB Id",
"TableB"."Name" as "TableB Name",
"TableB"."Number",
"TableC"."Id" as "TableC ID",
"TableC"."Name" as "TableC Name",
"TableC"."Number"
FROM   ("TableB" INNER JOIN "TableBjunctionTableC" B2C ON "TableB"."Id"=B2C."BaseId")
            INNER JOIN "TableC" ON B2C"RelatedId"="TableC"."Id"
ORDER BY "TableB"."Id"

How would I construct this?
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
BI Consultant
Most Valuable Expert 2011
Commented:
If you need data from a table joined with data from another table, but the other table doesn't always contain related data, then use LEFT OUTER JOIN.

Also, I'd advice to not use spaces in column names (or aliases).  It makes things a lot easier, as you can see down below.  (It's not common practise to use those double quotes.)

Here's a possible query:

select
      tableA.Id as tableA_Id,
      tableA.Name as tableA_Name,
      tableA.Number as tableA_Number,
      tableB.Id as tableB_ID,
      tableB.Name as tableB_Name,
      tableB.Number as tableB_Number,
      TableC.Id as TableC_ID,
      TableC.Name as TableC_Name,
      TableC.Number as tableC_Number
FROM tableA
LEFT OUTER JOIN tableAjunctionTableB A2B ON tableA.Id = A2B.BaseId
LEFT OUTER JOIN tableB ON A2B.RelatedId = tableB.Id
LEFT OUTER JOIN TableBjunctionTableC B2C ON TableB.Id = B2C.BaseId
LEFT OUTER JOIN TableC ON B2CRelatedId = TableC.Id

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial