CODEWITHSUNDEEP
sqlsql-server
Srikanth Erukulla
Srikanth Erukulla

Reputation: 49

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

Upvotes: 1

Views: 3597

Answers (3)

Y. M.
Y. M.

Reputation: 107

Just use Left join with union like this example 

Select TP.* 
  FROM ParentTable as TP LEFT JOIN ChildTable as TC 
  ON TC.c_ParentcommentID = TP.commentID AND TC.c_ParentcommentID = null 
  WHERE TP.userID='user1'
UNION ALL 
Select TC.c_CommnetID as CommentID , TC.c_userID  as userID , TC.c_Message as Message , TC.Date as 'Date'
  FROM ChildTable as TC  inner join ParentTable as TP 
  ON  TC.c_ParentcommentID = TP.commentID
  WHERE TC.userID='user1'

Upvotes: 0

Satwik Nadkarny
Satwik Nadkarny

Reputation: 5135

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!!!

Upvotes: 1

ScaisEdge
ScaisEdge

Reputation: 133360

Use Join

Inner join if the id always match 

 select 
   a.CommentID as CommentI
   ,a.userID, as userID
   , a.Message as Message
   , a.Date as date
   , b.c_commentID as parent__comment_id 
  from parent_comment as a
 inner join child_comment as b  on a.commentID = b.c_commentID

left join if not always match 

 select 
   a.CommentID as CommentI
   ,a.userID, as userID
   , a.Message as Message
   , a.Date as date
   , b.c_commentID as parent__comment_id 
  from parent_comment as a
 left join child_comment as b  on a.commentID = b.c_commentID

Upvotes: 1

Related Questions