How to get records from parent table and child table based on parent table id in SQL

Question

2.00/5 (1 vote)

See more:

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

Posted 17-Nov-16 19:37pm

prasanna204

Updated 17-Nov-16 22:16pm

Add a Solution

2 solutions

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Suvendu Shekhar Giri · Answer 1 · 2016-11-17T19:55:00

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.

SQL

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

StackQ · Answer 2 · 2016-11-17T22:16:00

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

C#

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

How to get records from parent table and child table based on parent table id in SQL

2 solutions

Solution 1

Solution 2

Add your solution here

Preview 0