Join SQL tables back together to create a report

We have a system that keeps track of packages in an MS SQL database. Each package contains 1-3 boxes. All packages have at least one box, "BoxSide". Additionally, they might also have a "BoxOuter" next to it, and that box optionally might have "BoxInner" inside of it. Each box has contents that we keep the data on.

So there are five separate tables total: One with the package information, one for each box (Side, Outer and Inner) and one table that has the data about the contents of each. They relate to each other as shown in the following diagram:

Table diagram

What I want to do is run a query that puts all of that data together in a report with a single record for each package and fields for the contents of each box, leaving non-applicable fields blank. Something like:

Package.*, BoxSide.Data1, Boxside.Data2, BoxOuter.Data1, BoxOuter.Data2, BoxInner.Data1, BoxInner.Data2

To reiterate:

I want a single record for every package
Every Package has a BoxSide
A Package MIGHT have a BoxOuter
A BoxOuter MIGHT have a BoxInner

I've tried various joins to link this data back together, but I seem to always end up with missing packages (if they didn't have a certain box type) or duplicate records of the same package multiple times for different box types. Too many failed attempts to list here.

Can somebody with some SQL-fu please help me with a query that would do this?

Solution

You need to use LEFT JOIN to get every record in Package regardless if it has corresponding values in the other tables:

    SELECT P.ID, p.Time, P.Type, P.Name, P.location,
    s.ID, sc.ID, sc.Data1, sc.Data2,sc.Data3
    o.ID, oc.ID, oc.Data1, oc.Data2, oc.Data3
    i.ID, ic.ID, ic.Data1, ic.Data2, ic.Data3
    FROM Package p
    LEFT JOIN BoxSide s ON p.ID = s.PackageID
    LEFT JOIN Contents sc ON s.ID = sc.ParentID
    LEFT JOIN BoxOuter o ON p.ID = o.PackageID
    LEFT JOIN Contents oc ON o.ID = oc.ParentID
    LEFT JOIN BoxInner i ON o.ID = i.BoxOuterID
    LEFT JOIN Contents ic ON i.ID = ic.ParentID

Note that you also have to LEFT JOIN the Contents table multiple times, each time with a different alias to link it to each parent table.