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

Question

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

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:

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

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

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):

Not sure how inner join would be the solution since the data at level 4 is optional. Inner join would cause the upper level data to be lost. — Moiz Tankiwala, Nov 13 '17 at 16:28
Is the equality of `CommentId` and `ManagementResponseId` in the desired output intentional or coincidental? — Caleth, Nov 13 '17 at 16:49

Caleth · Answer 1 · 2017-11-13T16:46:58.987

You need to specify that Comment.CommentId is equal to ManagementResponse.ManagementResponseId or either is null. That can be part of a JOIN or a separate WHERE

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
WHERE           ManagementResponse.ManagementResponseId = Comment.CommentId OR ManagementResponse.ManagementResponseId IS NULL OR Comment.CommentId IS NULL

This assumes that those IDs begin equal is the relationship you want to model. The example data seems to show this, but it could be a coincidence of how you assembled the example. Alternatively, if there is no relationship between Comment and ManagementResponse besides RLIId, something like

WITH CommentAndResponse AS (
    SELECT Comment.CommentId, Comment.CommentText, ManagementResponse.ManagementResponseId, ManagementResponse.ManagementResponseTest,
        COALESCE(Comment.RLIId, ManagementResponse.RLIId) AS RLIId,
        ROW_NUMBER() OVER (ORDER BY Comment.CommentId, ManagementResponse.ManagementResponseId, PARTITION BY Comment.RLIId, ManagementResponse.RLIId) AS rn
    FROM Comment
    FULL JOIN ManagementResponse ON 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 AND CommentAndResponse.rn = 1

thanks for the answer. One of my team members built upon your solution (which has a syntax issue) and got a working one. See the working solution marked as the answer. Your inputs helped us get there. — Moiz Tankiwala, Nov 15 '17 at 10:58

score 1 · Accepted Answer · answered Nov 14 '17 at 13:51

This query will give you the final output:

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

that was perfect. Good one! – Moiz Tankiwala Nov 15 '17 at 10:56 — Moiz Tankiwala, Nov 15 '17 at 10:56

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

2 Answers2