如何根据SQL中的父表id从父表和子表中获取记录 [英] How to get records from parent table and child table based on parent table id in SQL
问题描述
嗨朋友
我有两个表父table1和子table2。
Ex:table1列
UserId名称
1父母
table2列
UserId名称DOB
1 Child1 14-08-2014
1 Child2 15 -08-1987
现在我想根据用户ID得到这样的结果
Ex:
用户ID名称DOB
1家长---
1 Child1 14-08-2014
1 Child2 15-08-1987
如何获得此结果?
我尝试了什么:
我尝试使用左连接,但我不想在父行旁边显示子记录
我想先显示父记录,然后在下一行显示子记录我来.....等
首先,不确定为什么需要这样的结果!
您可能需要如果你说这些是父表和子表,那么这些表之间有关系的结果。
无论如何,如果你确定你需要这样的结果那么你可以简单地使用UNION
或UNION ALL
但是,我不知道你为什么要这样做。
SELECT *, NULL AS DOB FROM table1
UNION
< span class =code-keyword> SELECT * FROM table2
如果这不是您想要的,请清楚地告诉我们您的要求,以便我们建议更好的方法。
谢谢
与父母一起作为
(
- 这里你可以使用父母table的select statment
SELECT * FROM(VALUES(1,'Parent'),(2,'Parent'))AS P(UserId,Name)
),
儿童AS
(
- 这里你可以使用子表的选择语句
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)
),
- 使用此区块获取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南当'父母'那么0结束
我的O / P->
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 useUNION
orUNION 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
这篇关于如何根据SQL中的父表id从父表和子表中获取记录的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!