Coalesce Function Returning Duplicates

Question

I have a many-to-many relationship setup between three tables.

CompanyNotices (M-M) CompanyNoticesLocations (M-M) Locations

I would like to retrieve all rows from the CompanyNotices table for one CompanyID and aggregate all of the LocationNames (from the Locations table).

I have tried to use the Coalesce function to do this in a stored procedure but it is returning all LocationNames for each CompanyNotice row.

DECLARE @CompanyID INT
SET @CompanyID = 9
DECLARE @Location varchar(4000)

SELECT @Location = COALESCE(@Location + ', ', '') + L.LocationName 
FROM dbo.CompanyNotices AS CN
INNER JOIN dbo.CompanyNoticesLocations AS CNL ON CN.CompanyNoticeID = CNL.CompanyNoticeID
INNER JOIN dbo.Locations AS L ON CNL.LocationID = L.LocationID
WHERE CN.CompanyID = @CompanyID
GROUP BY L.LocationName 


SELECT CN.CompanyNoticeHeading, CN.CompanyNoticeText, IsHR, IsApprover, IsEmployee, @Location AS LocationName
FROM dbo.CompanyNotices AS CN
INNER JOIN dbo.CompanyNoticesLocations AS CNL ON CN.CompanyNoticeID = CNL.CompanyNoticeID
INNER JOIN dbo.Locations AS L ON CNL.LocationID = L.LocationID
WHERE CN.CompanyID = @CompanyID
GROUP BY CN.CompanyNoticeID, CN.CompanyNoticeHeading, CN.CompanyNoticeText, IsHR, IsApprover, IsEmployee

I do realise that when I am setting the @Location that it is not linked to each CompanyNotice, so I am wondering how I can incorporate the first SQL query into the second to COALESCE the LocationName for each CompanyNotice?

Thanks in advance.

Answer 1

Thanks Martin, using your example answer I have it working using the following code:

SELECT CN.CompanyNoticeHeading, CN.CompanyNoticeText, IsHR, IsApprover, IsEmployee, 
    Locations = STUFF
    (
        (
            SELECT ', ' + LocationName FROM dbo.Locations 
            WHERE LocationID IN 
                (SELECT LocationID FROM dbo.CompanyNoticesLocations WHERE CompanyNoticeID = CN.CompanyNoticeID) 
            FOR XML PATH ('')
        ),1,1,''
    )
    FROM dbo.CompanyNotices AS CN
    INNER JOIN dbo.CompanyNoticesLocations AS CNL ON CN.CompanyNoticeID = CNL.CompanyNoticeID
    INNER JOIN dbo.Locations AS L ON CNL.LocationID = L.LocationID
    WHERE CN.CompanyID = @CompanyID
    GROUP BY CN.CompanyNoticeID, CN.CompanyNoticeHeading, CN.CompanyNoticeText, IsHR, IsApprover, IsEmployee