sql - Query for parents and top two children of a hierarchyID table -

i'm trying parent , top left , top right children single table uses hiearchyid. been banging head on weeks , appreciate help.

it's single table stores 'facts' school debate team, each fact has child facts can either 'for' or 'against' parent fact. i'd parent , recent 'for' , recent 'against' fact summary page. instead i'm getting cross-union every combination of those.

here's 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 better."         2                         08:02 7              /1/2/3/                   3                         "best captain ever"         1                         08:03 32             /1/2/4/                   3                         "hate over-acting"      2                         08:04 43             /1/2/5/                   3                         "priceline great."       1                         08:05 44             /1/2/6/                   3                         "spock better too."     2                         08:06 

here's current query:

select p.text parenttext, p.timestamp parenttimestamp,              l.text lefttext,  max(l.timestamp) lefttimestamp,             r.text righttext, max(r.timestamp) righttimestamp     app.facts p      left outer join app.facts l              on (p.nodeid = (l.nodeid).getancestor(1))  , (l.sideid = 1 or l.sideid null)      left outer join app.facts r         on (p.nodeid = (r.nodeid).getancestor(1))  , (r.sideid = 2 or r.sideid null)  (p.text not null) , (p.nodelevel = 2)       group p.text, p.timestamp, l.text, l.timestamp, r.text, r.timestamp having l.timestamp = max(l.timestamp) , r.timestamp = max(r.timestamp) order p.timestamp desc, l.timestamp desc, r.timestamp desc 

here's i'd get:

parenttext      parenttimestamp lefttext                 lefttimestamp  righttext              righttimestamp ==========      =============== =====================   =============   =========               ============== "kirk rules"    08:00           "priceline great."   08:05          "spock better too."  08:06 ... "top fact xx"   11:00           'for' fact xx           11:01          'against' fact xx        11:01 

here's i'm getting:

parenttext  parenttimestamp lefttext                lefttimestamp   righttext                righttimestamp ==========  =============== =====================   =============   =========                ============== "kirk rules"    08:00       "priceline great."   08:05           "spock better too."   08:06 "kirk rules"    08:00       "priceline great."   08:05           "hate over-acting"    08:04 "kirk rules"    08:00       "priceline great."   08:05           "picard better."       08:02 "kirk rules"    08:00       "best captain ever"     08:03           "spock better too."   08:06 "kirk rules"    08:00       "best captain ever"     08:03           "hate over-acting"    08:04 "kirk rules"    08:00       "best captain ever"     08:03           "picard better."       08:02 "kirk rules"    08:00       "great actor!"          08:01           "spock better too."   08:06 "kirk rules"    08:00       "great actor!"          08:01           "hate over-acting"    08:04 "kirk rules"    08:00       "great actor!"          08:01           "picard better."       08:02 ... same thing rest of top level facts. 

notes:

1. top(1) work if there 1 parent, real table has many top level parents.
2. root facts level 2, top 2 tree levels placeholders.
3. i'm using t-sql on sql server 2014.

really appreciate hints or solutions!

i found work around adding 2 columns:

islatestforarg     bit  not null, islatestagainstarg bit  not null 

then changed query to:

select p.text parenttext, p.timestamp parenttimestamp,          l.text lefttext,  max(l.timestamp) lefttimestamp,         r.text righttext, max(r.timestamp) righttimestamp     app.facts p  left outer join app.facts l          on (p.nodeid = (l.nodeid).getancestor(1))  , ((l.sideid null) or (l.islatestforarg = 1))  left outer join app.facts r     on (p.nodeid = (r.nodeid).getancestor(1))  , ((r.sideid null) or (r.islatestagainstarg = 1)) 

obviously not ideal since every insert requires updating islatestfor/against flag.