UserId名称DOB 
 1 父NULL 
 1  Child1 14-08-2014 
 1  Child2 15-08-1987 
 2 父NULL 
 2  Child3 15-08-1987 
 2  Child4 15-08-1987 
 2  Child5 15-08-1987 

hi friends


I have two tables parent table1 and child table2.

Ex: table1 Columns

UserId Name
1 Parent

table2 Columns

UserId Name DOB
1 Child1 14-08-2014
1 Child2 15-08-1987


Now I want get result like this based on UserID

Ex:
UserID Name DOB
1 Parent ---
1 Child1 14-08-2014
1 Child2 15-08-1987


how to get this result?

What I have tried:

I tried with left join but I dont want to display child record beside parent row

I want to display parent record first after that in next line the child records will come.....etc

First of all, not sure why you need such a result!
You would probably need a result which has the relation between these tables if you are saying these are parent and child tables.

Anyway, if you are sure enough that you need such a result then you can simply use UNION or UNION ALL but, I have no idea why would you do that.

SELECT *,NULL AS DOB FROM table1
UNION
SELECT * FROM table2

If this is not what you were looking for, please let us know your requirement clearly so that we can suggest a better approach.

Thanks

Try this---

WITH Parent as
(
--Here you can use parent table's select statment
SELECT * FROM (VALUES(1,'Parent'),(2,'Parent'))AS P(UserId,Name)
),
Child AS
(
--Here you can use child table's select statment
SELECT * FROM
(VALUES
(1,'Child1','14-08-2014'),
(1,'Child2','15-08-1987'),
(2,'Child3','15-08-1987'),
(2,'Child4','15-08-1987'),
(2,'Child5','15-08-1987')
)AS T(UserId,Name,DOB)
),
--Use This block to get O/P
FinalOutput AS
(
SELECT UserId,Name,NULL AS DOB FROM Parent C
UNION ALL
SELECT C1.UserId,C1.Name,C1.DOB
FROM Child C1 RIGHT JOIN Parent C ON C.UserId=C1.UserId
)
SELECT * FROM FinalOutput
ORDER BY UserId,CASE Name WHEN 'Parent' THEN 0 ELSE 1 END

My O/P->

UserId	Name	DOB
1	Parent	NULL
1	Child1	14-08-2014
1	Child2	15-08-1987
2	Parent	NULL
2	Child3	15-08-1987
2	Child4	15-08-1987
2	Child5	15-08-1987