JOIN + Subselect over M:N table

by Alexander Zeitler   Last Updated May 22, 2018 10:06 AM

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.

Tags : sql-server

Answers 1

Why don't you just omit the WHERE... clause altogether? And then just JOIN all relevant tables as in:

    LEFT JOIN Packaging
        ON Product.PackagingID = Packaging.ID
    LEFT JOIN LabelsForPackaging 
        ON LabelsForPackaging.PackagingID = Packaging.ID
    INNER JOIN Label
        ON LabelsForPackaging.LabelID = Label.ID
May 22, 2018 09:32 AM

Related Questions

creating index on big table(s)

Updated September 03, 2018 18:06 PM

Linked Server Error on SQL Agent Job

Updated March 16, 2016 08:02 AM