More trouble with STUFF

Question

I posted this a while back: Can't seem to understand why STUFF isn't working and @bluefeet was able to help me out very graciously...

Now I've got a similiar issue with this STUFF()! Basically I have three tables one table has Company names Company. Another table has Issues Issue and finally the third table has Issues that happened in a Company, call it PAndCIssueLocation.

For instance assume you have an issue that happened at two plants...so you end up with one Issue record like so:

Issue Table
ID  Title
1   Robot is down

And you've got a company table:

Company Table
ID  Name
1   Auburn Hills
2   Detroit

And finally the third table:

IssueID  CompanyID
1        1
1        2

That is Issue ID = 1 happened at two company locations (Detroit, Auburn Hills). I was trying to use STUFF in a report so I can comma seperate these two items (company names) however, what is happening is the same company name is repeating itself X amount of times (I believe X is the total number of issues in my database). Here is the code I have:

SELECT
    i.IssueID AS IssueID,
    i.PAndCAuditDate AS AuditDate,
    i.PAndCValidationCompletedOn AS ValidationCompletedOnDate,
    i.CustomerIssueNumber AS OldId,
    i.PAndCValidatedBy AS ValidatedBy,
    i.PAndCValidatedComments AS ValidatedComments,
    i.Title AS Title,
    i.OpenDate AS IssueOpenDate,
    i.PAndCAuditors AS Auditors,
    i.PAndCAuditees AS Auditees,
    i.Description AS Description,
    i.PAndCRootCause AS RootCause,
    i.PAndCContainmentAndCorrectiveActions AS ContainmentAndCorrectiveActions,
    i.PAndCPreventiveActions AS PreventiveActions,
    DATEDIFF(d, i.TargetDate, GetDate()) AS DaysOverdue,
    i.TargetDate AS IssueTargetDate,
    i.ClosedDate AS IssueClosedDate,
    CASE WHEN i.ClosedDate IS NULL AND DATEADD(d, DATEDIFF(d, 0, i.TargetDate), 0) < DATEADD(d, DATEDIFF(d, 0, GetDate()), 0) THEN 1 ELSE 0 END AS IsOverdue,
    s1.CompanyHasType
    --s1.Name
FROM
    Issue i
LEFT JOIN
(
SELECT c.IssueID, STUFF((
    SELECT ', ' + ct.Name + ' - ' + ct.ShippingAddressCity + ', ' + ct.ShippingAddressRegion + ' ' + ct.CountryID FROM Company ct
     INNER JOIN PAndCIssueLocation cht
     ON cht.CompanyID = ct.CompanyID INNER JOIN Issue c
     ON c.IssueID = cht.IssueID
         FOR XML PATH(''), TYPE).value('.[1]',
       'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM dbo.Issue AS c
GROUP BY c.IssueID  
) s1 ON
s1.IssueID = i.IssueID

Notice this portion where the STUFF is happening:

LEFT JOIN
(
SELECT c.IssueID, STUFF((
    SELECT ', ' + ct.Name + ' - ' + ct.ShippingAddressCity + ', ' + ct.ShippingAddressRegion + ' ' + ct.CountryID FROM Company ct
     INNER JOIN PAndCIssueLocation cht
     ON cht.CompanyID = ct.CompanyID INNER JOIN Issue c
     ON c.IssueID = cht.IssueID
         FOR XML PATH(''), TYPE).value('.[1]',
       'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM dbo.Issue AS c
GROUP BY c.IssueID  
) s1 ON
s1.IssueID = i.IssueID

This is the portion that is giving me an issue. I know I have to somehow relate the issue back but I don't know exactly how!

Any help is much appreciated...

Answer 1

You could move the subquery into the main query and add where cht.issueid = i.issueid. Optionally you could add distinct and an order by depending on how the data are related.

SELECT
    i.IssueID AS IssueID,
    i.PAndCAuditDate AS AuditDate,
    i.PAndCValidationCompletedOn AS ValidationCompletedOnDate,
    i.CustomerIssueNumber AS OldId,
    i.PAndCValidatedBy AS ValidatedBy,
    i.PAndCValidatedComments AS ValidatedComments,
    i.Title AS Title,
    i.OpenDate AS IssueOpenDate,
    i.PAndCAuditors AS Auditors,
    i.PAndCAuditees AS Auditees,
    i.Description AS Description,
    i.PAndCRootCause AS RootCause,
    i.PAndCContainmentAndCorrectiveActions AS ContainmentAndCorrectiveActions,
    i.PAndCPreventiveActions AS PreventiveActions,
    DATEDIFF(d, i.TargetDate, GetDate()) AS DaysOverdue,
    i.TargetDate AS IssueTargetDate,
    i.ClosedDate AS IssueClosedDate,
    CASE WHEN i.ClosedDate IS NULL 
      AND DATEADD(d, DATEDIFF(d, 0, i.TargetDate), 0) 
        < DATEADD(d, DATEDIFF(d, 0, GetDate()), 0) 
        THEN 1 ELSE 0 END AS IsOverdue,
    --s1.CompanyHasType
    --s1.Name
    , STUFF((
    SELECT distinct ', ' + ct.Name + ' - ' + ct.ShippingAddressCity 
        + ', ' + ct.ShippingAddressRegion + ' ' + ct.CountryID 
    FROM Company ct
     INNER JOIN PAndCIssueLocation cht ON cht.CompanyID = ct.CompanyID 
     --INNER JOIN Issue c ON c.IssueID = cht.IssueID
     where cht.IssueId = i.IssueId
     --order by ct.name
         FOR XML PATH(''), TYPE).value('.[1]',
       'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM
    Issue i
/*LEFT JOIN
(
SELECT c.IssueID, STUFF((
    SELECT ', ' + ct.Name + ' - ' + ct.ShippingAddressCity + ', ' 
        + ct.ShippingAddressRegion + ' ' + ct.CountryID FROM Company ct
     INNER JOIN PAndCIssueLocation cht
     ON cht.CompanyID = ct.CompanyID INNER JOIN Issue c
     ON c.IssueID = cht.IssueID
         FOR XML PATH(''), TYPE).value('.[1]',
       'nvarchar(max)'), 1, 2, '') AS CompanyHasType
FROM dbo.Issue AS c
GROUP BY c.IssueID  
) s1 ON
s1.IssueID = i.IssueID
*/