一个表具有多个外键字段时如何联接多个表 [英] How to join multiple tables while one table has multiple foreign key fields

问题描述

这里有三个表，我想加入它们；但是在加入这些表时有点困惑.

表名称:New_Reg

Here are three tables and i want to join them; but bit confused while joining these tables..

Table Name: New_Reg

Reg_ID(PK) old_society_ID(FK) new_society_ID(FK)
1         1                  2

表名称:Society

Table Name: Society

Society_ID(PK) Society_Name Center_ID(FK)
1                    AAA          1
2                    BBB          2                

表名称:Center

Table Name: Center

Center_ID(PK) Center_Name 
1                  Center1
2                  Center2

最后我想要这些以下输出

Finally i want these following output

Reg_ID old_society_ID Old_Society Old_Center new_society_ID New_Society New_Center
1         1              AAA        Center1     2             BBB         Center2

推荐答案

请参阅下面的查询.可能会帮助您.

Hi,

See the below Query. Might be help you.

SELECT     New_Reg.Reg_ID, New_Reg.old_society_ID, Society.Society_Name AS Old_Society, Center.Center_Name AS Old_Center, New_Reg.new_society_ID, 
                      Society_1.Society_Name AS New_Society, Center_1.Center_Name AS New_Center
FROM         New_Reg INNER JOIN
                      Society ON New_Reg.old_society_ID = Society.Society_ID INNER JOIN
                      Center ON Society.Center_ID = Center.Center_ID INNER JOIN
                      Society AS Society_1 ON New_Reg.new_society_ID = Society_1.Society_ID INNER JOIN
                      Center AS Center_1 ON Society_1.Center_ID = Center_1.Center_ID

尝试一下:

Try this:

SELECT T1.New_Reg, T1.old_society_ID, T1.Old_Society, T1.Old_Center,
                       T2.new_society_ID, T2.New_Society, T2.New_Center
FROM (SELECT NR.Reg_ID, NR.old_society_ID, S.Society_Name AS Old_Society, C.Center_Name AS Old_Center
	FROM New_Reg AS NR LEFT OUTER JOIN Society AS S ON NR.old_society_ID = S.Society_ID
			LEFT JOIN Center AS C ON S.Center_ID = C.Center_ID ) AS T1
    LEFT JOIN (SELECT NR.Reg_ID, NR.new_society_ID, S.Society_Name AS New_Society, C.Center_Name AS New_Center
				FROM New_Reg AS NR
				LEFT OUTER JOIN Society AS S ON NR.new_society_ID = S.Society_ID
					LEFT JOIN Center AS C ON S.Center_ID = C.Center_ID) AS T2
	ON T1.Reg_ID = T2.Reg_ID

我想是一个有点复杂的查询，我想有很多连接
但这可以达到结果

A bit complext query I suppose with lots of join
But this achieves the result

select r.reg_id,r.old_society_id,s.society_name,c.center,
r.new_society_id,s1.society_name,c1.center
from reg r left join society s
 on (r.old_society_id = s.society_id) left join society s1
on (r.new_society_id = s1.society_id) left join center c
on(s.center_id = c.center_id) left join center c1
on(s1.center_id = c1.center_id)
where reg_id = 1

不确定性能.一些专家可能会发表评论，但这将达到目的

希望能有所帮助.如果是这样，请将答案标记为解决方案和/或投票.

谢谢
Milind

Not sure about performace. Some expert can comment probably but this will serve the purpose

Hope that helps. If it does, mark the answer as solution and/or upvote.

Thanks
Milind

这篇关于一个表具有多个外键字段时如何联接多个表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！