How would i create a report involving multiple many to many relationships

Posted on

Question :

Lets say table A has a many to many relationship with table B, and table B has a many to many relationship with C. Two junction tables are created for AB and BC to form the many to many relationships.
How would i query these tables so that i can return all the related records between tables A, B, and C?

Is a setup like this a sign of bad design?

Edit: To list my specific issue, I have a table of electronic devices. Each device could have a feature such as wifi, and each feature could apply to that device. Now each feature could pose a security risk, and that same risk could apply to multiple features. So I have many devices, with many features, and many security risks.
My goal is to get the results of a question in this form “For each device, show me each feature, and the security risks that it creates”.

Answer :

I think it is a sound design, based on what is called an associative entity. To query:

Select A.FieldList..., B.FieldList..., C.FieldList...
   From A
   Join AB On AB.AID = A.ID
   Join B  On B.ID   = AB.BID
   Join BC On BC.BID = B.ID
   Join C  On BC.CID = C.ID

Leave a Reply

Your email address will not be published. Required fields are marked *