Query for parents and top two children of a hierarchyID table

Question

I'm trying to get the Parent and its top left and top right children from a single table that uses HiearchyID. Been banging my head on it for weeks and would appreciate any help.

It's a single table that stores 'facts' for a school debate team, each fact has child facts that can either be 'for' or 'against' the parent fact. I'd like to get the parent and the most recent 'for' and most recent 'against' fact for a summary page. Instead I'm getting a cross-union with every combination of those.

Here's my table:

factID (key)    nodeID (hierarchyID)    nodeLevel(computed column) text                        side(1=for, 2=against)  timestamp
=======         ======                   =========                 ====                        =====                     =========
1              /1/2/                     2                         "Kirk rules"                1                         08:00
3              /1/2/1/                   3                         "Great actor!"              1                         08:01
5              /1/2/2/                   3                         "Picard is better."         2                         08:02
7              /1/2/3/                   3                         "best captain ever"         1                         08:03
32             /1/2/4/                   3                         "hate his over-acting"      2                         08:04
43             /1/2/5/                   3                         "PriceLine is great."       1                         08:05
44             /1/2/6/                   3                         "Spock was better too."     2                         08:06

Here's my current query:

SELECT P.text AS parentText, P.timeStamp AS parentTimeStamp,  
           L.text AS leftText,  MAX(L.timeStamp) AS leftTimeStamp, 
           R.text AS rightText, MAX(R.timeStamp) AS rightTimeStamp   

FROM app.Facts AS P 
    LEFT OUTER JOIN app.Facts AS L 
            ON (P.nodeID = (L.nodeID).GetAncestor(1))  AND (L.sideID = 1 OR L.sideID IS NULL)

    LEFT OUTER JOIN app.Facts AS R
        ON (P.nodeID = (R.nodeID).GetAncestor(1))  AND (R.sideID = 2 OR R.sideID IS NULL)

WHERE (P.text IS NOT NULL) AND (P.nodeLevel = 2)     

GROUP BY P.text, P.timeStamp, L.text, L.timeStamp, R.text, R.timeStamp
HAVING L.timeStamp = MAX(L.timeStamp) AND R.timeStamp = MAX(R.timeStamp)
ORDER BY P.timeStamp DESC, L.timeStamp DESC, R.timeStamp DESC

Here's what I'd like to get:

parentText      parentTimeStamp leftText                 leftTimeStamp  rightText              rightTimeStamp
==========      =============== =====================   =============   =========               ==============
"Kirk rules"    08:00           "PriceLine is great."   08:05          "Spock was better too."  08:06
...
"top fact xx"   11:00           'for' fact xx           11:01          'against' fact xx        11:01

Here's what I'm getting:

parentText  parentTimeStamp leftText                leftTimeStamp   rightText                rightTimeStamp
==========  =============== =====================   =============   =========                ==============
"Kirk rules"    08:00       "PriceLine is great."   08:05           "Spock was better too."   08:06
"Kirk rules"    08:00       "PriceLine is great."   08:05           "hate his over-acting"    08:04
"Kirk rules"    08:00       "PriceLine is great."   08:05           "Picard is better."       08:02
"Kirk rules"    08:00       "best captain ever"     08:03           "Spock was better too."   08:06
"Kirk rules"    08:00       "best captain ever"     08:03           "hate his over-acting"    08:04
"Kirk rules"    08:00       "best captain ever"     08:03           "Picard is better."       08:02
"Kirk rules"    08:00       "Great actor!"          08:01           "Spock was better too."   08:06
"Kirk rules"    08:00       "Great actor!"          08:01           "hate his over-acting"    08:04
"Kirk rules"    08:00       "Great actor!"          08:01           "Picard is better."       08:02
...
same thing with rest of the top level facts.

Notes:

TOP(1) would work if there were only one parent, but my real table has many top level parents.
Root facts are always level 2, the top two tree levels are basically just placeholders.
I'm using T-SQL on SQL Server 2014.

Really appreciate any hints or solutions!

score 0 · Accepted Answer · answered Jul 17 '15 at 13:29

I found a work around by adding two columns:

isLatestForArg     BIT  NOT NULL,
isLatestAgainstArg BIT  NOT NULL

Then changed my query to:

SELECT P.text AS parentText, P.timeStamp AS parentTimeStamp,  
       L.text AS leftText,  MAX(L.timeStamp) AS leftTimeStamp, 
       R.text AS rightText, MAX(R.timeStamp) AS rightTimeStamp   

FROM app.Facts AS P 
LEFT OUTER JOIN app.Facts AS L 
        ON (P.nodeID = (L.nodeID).GetAncestor(1))  AND ((L.sideID IS NULL) OR (L.isLatestForArg = 1))

LEFT OUTER JOIN app.Facts AS R
    ON (P.nodeID = (R.nodeID).GetAncestor(1))  AND ((R.sideID IS NULL) OR (R.isLatestAgainstArg = 1))

Obviously not ideal since now every INSERT requires updating the isLatestFor/Against flag.

Query for parents and top two children of a hierarchyID table

1 Answers1