2

How do I select all things from the many of a one to many relationship, several layers down in many one-to-many relationships? I'm not sure how to nest all those left joins.

Example

There are many Institutions
Each Institution has many Departments
Each Department has many Forums
Each Forum has many Users
Each User has many Posts
Each Post has many Comments

Find all Comments for an Institution.

I googled around, but I'm not sure what the name for this is - chain nested one to many relationship queries? The closest I found was SQL left join vs multiple tables on FROM line?

Community
  • 1
  • 1
xxjjnn
  • 14,591
  • 19
  • 61
  • 94

1 Answers1

3

Without knowing your schema and making some assumptions:

SELECT c.*
FROM
    Institution i
    INNER JOIN Department d ON d.InstitutionID = i.InstitutionID
    INNER JOIN Forums f ON f.DepartmentID = d.DepartmentID
    INNER JOIN `User` u ON u.ForumID = f.ForumID
    INNER JOIN Post p ON p.UserID = u.UserID
    INNER JOIN Comment c ON c.PostID = p.PostID
WHERE
    i.InstitutionID = 42
Sean Bright
  • 118,630
  • 17
  • 138
  • 146