sql - Problem with JOINs on selection of related data

Question

I have three entity types (let's call them A, B, C) persisted on three tables of my database.

Each entity type has a relation with the other two entities. Relations are persisted in three tables of the DB as well (let's call them AB, AC, BC), where every record is a couple of IDs of the respective entities.

Relations A-B are one-to-many and are mandatory: every A has at least one relation with a B, every B has a relation with an A.

Relations A-C and B-C are many-to-many and are optional: there can be As without relation with Cs, there can be Bs without relations with Cs. I cannot change this schema.

I need to build a table with all the As and their related data. Every row of the table must contain only related data or NULLs where there are no relations. I thought I would be fine with something like:

SELECT * -- let's omit columns for simplicity
FROM      AB
LEFT JOIN BC ON BC.IdB = AB.IdB
LEFT JOIN AC ON AC.IdA = AB.IdA AND
                AC.IdC = BC.IdC
INNER JOIN A ON A.Id = AB.IdA
INNER JOIN B ON B.Id = AB.IdB
LEFT JOIN  C ON C.Id = AC.IdC

and then filtering with a WHERE clause. My problem is I don't get how, which makes me think I am approaching the problem in the wrong way.

Any hint would be appreciated, thank you in advance.

Answer 1

等待大神答复