user6503334 user6503334 - 4 months ago 12
SQL Question

How to get Parent and Child Table data as a single query in Sql

I want to get all data from 2 table based on

userID=user1

from parent and child table as i shown in below image
enter image description here

Answer

I think this should work:

SELECT      parent.CommentID as CommentID,
            parent.userID as userID, 
            parent.Message as Message,
            parent.Date as date,
            NULL as ParentCommentID
FROM        #ParentComment parent
WHERE       UserID = 'user1'

UNION

SELECT      child.c_commentID as CommentID,
            child.c_userID as userID, 
            child.c_message as Message,
            child.c_Date as date,
            child.c_parentcommentID as ParentCommentID
FROM        #ChildComment child
WHERE       child.c_userID = 'user1'

Here's an SQL fiddle : http://sqlfiddle.com/#!9/3bb46/2

Hope this helps!!!

Comments