Geek asks and answers

Three way join using a single SQL table

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

ID | dateAdded | parentPostID | postBody

A user in the system can create a request, receive a response, and then other users can comment on the response. That is, Request <= 1: many => Reponse <= 1: many => Comment (I think, StackOverlow Question> Answer> Comment model-like).

All user messages (request, response, and comment) are represented by UserPost lines , where Request has parentPostID = null;; Responses parentPostIDare the request identifier, and the comment parentPostIDis the response identifier.

I need to output everything in simple ways:

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

: SQL ?

(UserPosts) Requests [join] (UserPosts) [join] (UsersPosts) , , .

: # Linq?

+3
1

LINQ. . , . hierarchyid, :

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) + '/'
    )

( ) /GrandParent/Parent/Child/ - , . , (up_2nd.ID null, 2 , ), /Parent/Child/ - , 1- COALESCE , , ( up_1st.ID up_2nd.ID ), COALESCE , /ID/.

:

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

, , ( )

+5

More articles:


All Articles