Xml stuff

Transact-SQL
1

For some reason I can never get my head around XML stuff , what i'm trying to achieve is the respective fault codes for the returned items . all i get is a stuff of all of the fault codes not just the fault codes that refer to to a single product , Any help as always greatly appreciated ,

SELECT        Products.ProductId, 
Products.ProductDescription,
 ReturnedItems.QuantityReturned,
--  FaultCodes.FaultCodeId,
  -- ReturnedItems.CreatedDate,
    FaultCodes = STUFF ((SELECT ',' + FaultCodeId FROM  FaultCodes INNER JOIN 
                   ReturnedItems ON ReturnedItems.ActualFaultCode = FaultCodes.FaultCode INNER JOIN Products ON ReturnedItems.ReportedProduct = Products.Product FOR XML PATH ('')), 1, 1, ''  ) 



FROM            ReturnedItems INNER JOIN
                         FaultCodes ON ReturnedItems.ReportedFaultCode = FaultCodes.FaultCode AND ReturnedItems.ActualFaultCode = FaultCodes.FaultCode INNER JOIN
                         Products ON ReturnedItems.ReportedProduct = Products.Product AND ReturnedItems.ActualProduct = Products.Product AND ReturnedItems.ReplacementProduct = Products.Product


					WHERE 	 YEAR(ReturnedItems.CreatedDate) = YEAR(getdate())
2

Without seeing the structure of your data, it is hard to figure out what might be wrong, but my guess is that your inner query where you construct the XML is not correlated to the outer query. So perhaps something like this might work.

SELECT
    p1.ProductId,
    p1.ProductDescription,
    r1.QuantityReturned,
    --  FaultCodes.FaultCodeId,
    -- ReturnedItems.CreatedDate,
    FaultCodes = STUFF(
                     (
                         SELECT
                             ',' + FaultCodeId
                         FROM
                             fc2
                             INNER JOIN r2
                                 ON r2.ActualFaultCode = fc2.FaultCode
                         WHERE
                             r2.ReportProduct = p1.Product
                         FOR XML PATH('')
                     ), 1, 1, ''
                      )
FROM
    ReturnedItems         r1
    INNER JOIN FaultCodes fc1
        ON r1.ReportedFaultCode = fc1.FaultCode
           AND r1.ActualFaultCode = fc1.FaultCode
    INNER JOIN Products   p1
        ON r1.ReportedProduct = p1.Product
           AND r1.ActualProduct = p1.Product
           AND r1.ReplacementProduct = p1.Product
WHERE
    YEAR(r1.CreatedDate) = YEAR(GETDATE());
3

You SIR are a legend , :grinning: