Three-way join using one SQL table

Question

Suppose there is a MSSQL table, UserPost, that represents something user has posted, with these fields:

ID | dateAdded | parentPostID | postBody

A user in the system could create a Request, receive a Response, and then other users could Comment on the Response. Ie, Request <=1:many=> Reponse <=1:many=> Comment (think StackOverlow's Question > Answer > Comment model-like).

All user posts (Request, Response and Comment) are represented by UserPost rows, where Request has parentPostID = null;; Responses' parentPostID is the Request's ID, and Comment's parentPostID is the ID of the Response.

I need to output everything in a simple fashion:

Request 1
- Response A
-- Comment (i)
-- Comment (ii)
- Response B
-- Comment (i)
Request 2
...

Question: which SQL statement returns the needed information in the most usable way?

I'm struggling to write a three-way join between (UserPosts) as Requests [join] (UserPosts) as Responses [join] (UsersPosts) as Comments but am not sure this is the easiest way.

Bonus: is it possible to do this using C# Linq?

Answer 1

Can't think of a way to do this in LINQ. I've removed unused columns. Luckily this is a bounded hierarchy. I'm using the new hierarchyid data type, which has the desired sort order:

create table UserPosts (
    ID int not null,
    ParentID int null
)
go
insert into UserPosts (ID,ParentID)
select 1,null union all
select 2,null union all
select 3,1 union all
select 4,2 union all
select 5,3 union all
select 6,1 union all
select 7,6
go
select
    *
from
    UserPosts up
        left join
    UserPosts up_1st
        on
            up.ParentID = up_1st.ID
        left join
    UserPosts up_2nd
        on
            up_1st.ParentID = up_2nd.ID
order by
    CONVERT(hierarchyid,
    COALESCE('/' + CONVERT(varchar(10),up_2nd.ID),'') +
    COALESCE('/' + CONVERT(varchar(10),up_1st.ID),'') +
    '/' + CONVERT(varchar(10),up.ID) + '/'
    )

HierarchyIDs (as strings) look like /GrandParent/Parent/Child/ - so we construct values that look like this. Obviously, if we don't have a grandparent (up_2nd.ID is null, since we can't achieve 2 left joins as described), then we just want to construct /Parent/Child/ - this is what the 1st COALESCE is helping us achieve. Similarly, if we can't find any parents (both up_1st.ID and up_2nd.ID are null), then both of the COALESCEs just turn into empty strings, and we end up construcing /ID/.

---

You can add:

CASE
    WHEN up_2nd.ID is not null then 'Comment'
    WHEN up_1st.ID is not null then 'Response'
    ELSE 'Request'
END as Level

to your select list, if you want to track what level the item is (or use numerics instead, if desired)