Search code examples
sqlsql-serverleft-joinright-join

Want data added to the Query via Left Outer Joint to NOT repeat

I have a database structure (ER diagram below) that has three level of hierarchical data and the fourth level of optional data. enter image description here

If I write a query to get de-normalized data of three levels - level 1 to level 3 with sample data across three tables shown as below:

enter image description here

When queried, this layout of the data is very straight forward and as expected as below:

enter image description here

Upon running the below query, I get the following output (And I have tried various combinations by clubbing the set of L1 to L4 and moving one L4 out as a another query and then joining the set L1 - L4 etc.) - again this is on the expected lines.

SELECT        [Group].GroupId, [Group].GroupName, Category.CategoryId, Category.CategoryName, RLI.RLIId, RLI.RLIText, Comment.CommentId, Comment.CommentText, ManagementResponse.ManagementResponseId, 
                         ManagementResponse.ManagementResponseTest
FROM            Category INNER JOIN
                         [Group] ON Category.GroupId = [Group].GroupId INNER JOIN
                         RLI ON Category.CategoryId = RLI.CategoryId LEFT OUTER JOIN
                         ManagementResponse ON RLI.RLIId = ManagementResponse.RLIId LEFT OUTER JOIN
                         Comment ON RLI.RLIId = Comment.RLIId

enter image description here

However, I need data in the following format - and this is what I am unable to figure out how to get (I don't want the level 4 data to repeat as I add additional level 4 data via left outer joins): enter image description here

Solution

  • This query will give you the final output: enter image description here

    WITH CommentAndResponse AS (

SELECT Comment.CommentId,
         Comment.CommentText, 
         ManagementResponse.ManagementResponseId, 
         ManagementResponse.ManagementResponseTest,
    COALESCE(Comment.RLIId, ManagementResponse.RLIId) AS RLIId
 FROM (
    (SELECT Comment.CommentId, 
            Comment.CommentText,
            Comment.RLIId,
            ROW_NUMBER() OVER (PARTITION BY Comment.RLIId ORDER BY Comment.CommentId) AS CommentRowNumber
    FROM Comment) AS Comment
    FULL JOIN
    (SELECT  ManagementResponse.ManagementResponseId, 
            ManagementResponse.ManagementResponseTest,
            ManagementResponse.RLIId,
            ROW_NUMBER() OVER (PARTITION BY ManagementResponse.RLIId ORDER BY ManagementResponse.ManagementResponseId) AS ManagementResponseRowNumber   
    FROM ManagementResponse) AS ManagementResponse
    ON Comment.CommentRowNumber = ManagementResponse.ManagementResponseRowNumber AND Comment.RLIId = ManagementResponse.RLIId ) 
    )

SELECT          [Group].GroupId, [Group].GroupName, Category.CategoryId, Category.CategoryName, RLI.RLIId, RLI.RLIText, CommentAndResponse.CommentId, CommentAndResponse.CommentText, CommentAndResponse.ManagementResponseId, CommentAndResponse.ManagementResponseTest
FROM            [Category]
INNER JOIN      [Group] ON Category.GroupId = [Group].GroupId 
INNER JOIN      [RLI] ON Category.CategoryId = RLI.CategoryId 
LEFT OUTER JOIN [CommentAndResponse] ON RLI.RLIId = CommentAndResponse.RLIId