JOIN + Subselect over M:N table I have four tables Product, Packaging, LabelsForPackaging and Label

I need to query all Products with their Packaging and Labels (should result in multiple rows)

Product has Packaging1Id and LabelsForPackaging has the PackagingId and the LabelId.

I tried this

select * from Product
left join Packaging on Product.Packaging1Id = Packaging.Id
left join
(
    select * from LabelsForPackaging 
    inner join Label on LabelsForPackaging.LabelId = Label.Id
    where LabelsForPackaging.PackagingId = -- what to use here?
) as P
on Product.Packaging1Id = P.Id


If I try to use where LabelsForPackaging.PackagingId = Packaging.Id in the subselect, I get this error:

The multi-part identifier "Packaging.Id" could not be bound.
Why don't you just omit the WHERE... clause altogether? And then just JOIN all relevant tables as in:

SELECT * FROM Product
    LEFT JOIN Packaging
        ON Product.PackagingID = Packaging.ID
    LEFT JOIN LabelsForPackaging 
        ON LabelsForPackaging.PackagingID = Packaging.ID
    INNER JOIN Label
        ON LabelsForPackaging.LabelID = Label.ID