表中的多个列的SQL连接 [英] Multiple column SQL joins in a table
问题描述
您好，首次发布的一个问题，所以请原谅任何错事......
我一直在考虑使用ASP.NET和SQL Server，我们基本上要做的就是创建一个表单，我们可以通过绑定到SQL数据源的单选按钮列表中选择一个政党在大学的转让。在此基础上选择，下拉列表，然后与国会议员谁是一方的一部分选择的无线按钮列表的列表填充。然后，您可以选择一个MP，并查看在议会中他或她的个人信息，他们的角色和通过一些链接到各种SQL数据源的数据控件问/视图的问题。
Hi there, first time posting a question so please excuse any wrongdoings... I have been given an assignment at university using ASP.NET and SQL Server, what we basically have to do is create a webform where we can select a political party via a radio button list bound to a SQL data source. Based on this selection, a drop-down list is then populated with a list of MPs who are a part of the party selected the radio button list. You can then select an MP and view his or her personal details, their roles in parliament and ask/view questions via a number of data controls linked to various SQL data sources.
我取得良好的进展，并试图利用一切资源，我可以找得到解决方案，但很可惜我已经打了一个墙选择MP后观看了议会的作用。
I'm making good progress and trying to use whatever resources I can find to get solutions but alas I've hit a wall with viewing the parliamentary roles after selecting the MP.
我有一个在这里涉及到3个表：
I have 3 tables which are involved here:
MP MP_ROLES ROLE
------------- --------------- ----------------
MP_NUM (PK) MP_NUM (PK, FK) ROLE_NUM (PK)
MP_FIRST ROLE_1 (FK) ROLE_DESCRIPTION
MP_LAST ROLE_2 (FK)
MP_EMAIL ROLE_3 (FK)
MP_CONTACTADD ROLE_4 (FK)
MP_PHOTO
PARTY_NUM(FK)
（其中 ROLE_1 到 ROLE_4 参考 ROLE.ROLE_NUM ）
我有这势必哪些查询 MP_ROLES 的SQL数据源的 DetailsView控件控制。什么我基本上试图做的是与相应的<$替换 ROLE_1 ， ROLE_2 的每个值，等等。通过 ROLE_NUM 角色中引用> C $ C> ROLE_DESCRIPTION 值表（如 MP_ROLE.ROLE_1 = ROLE.ROLE_NUM AND MP_ROLE.ROLE_2 = ROLE.ROLE_NUM AND MP_ROLE.ROLE_3 = ROLE.ROLE_NUM AND MP_ROLE.ROLE_4 = ROLE.ROLE_NUM ）。现在穿过文本框我挖我知道更简单的应用程序，你可以做什么，我试图与 INNER JOIN 函数来完成，但是因为我还在学习我的避风港'T完全得到语法整理做我想做的事情。
I have a DetailsView control which is bound to an SQL data source which queries MP_ROLES. What I'm basically trying to do is replace each value in ROLE_1, ROLE_2, etc. with the corresponding ROLE_DESCRIPTION value referenced by ROLE_NUM in the ROLE table (as MP_ROLE.ROLE_1 = ROLE.ROLE_NUM AND MP_ROLE.ROLE_2 = ROLE.ROLE_NUM AND MP_ROLE.ROLE_3 = ROLE.ROLE_NUM AND MP_ROLE.ROLE_4 = ROLE.ROLE_NUM). Now through my digging through textboxes I know in more simple applications you can do what I'm trying to do with the INNER JOIN function, however as I'm still learning I haven't quite got the syntax sorted to do what I want to do.
SELECT MP_ROLES.MP_NUM, MP_ROLES.ROLE_1, MP_ROLES.ROLE_2, MP_ROLES.ROLE_3, MP_ROLES.ROLE_4
FROM MP_ROLES
INNER JOIN ROLE ON MP_ROLES.ROLE_1 = ROLE.ROLE_NUM
INNER JOIN ROLE AS ROLE_3 ON MP_ROLES.ROLE_2 = ROLE.ROLE_NUM
INNER JOIN ROLE AS ROLE_2 ON MP_ROLES.ROLE_3 = ROLE.ROLE_NUM
INNER JOIN ROLE AS ROLE_1 ON MP_ROLES.ROLE_4 = ROLE.ROLE_NUM
WHERE (MP_ROLES.MP_NUM = @MP_NUM)
我已经挂了本作的最后8试图找到一个解决方案，因此，如果有任何人谁能够告诉我在哪里我会错了或者是一种足以提供一个解决方案，那将是非常AP preciated：）
I've been hung up on this for the last 8 trying to find a solution so if there is anyone who is able show me where I'm going wrong or perhaps kind enough to provide a solution it would be very much appreciated :)
推荐答案
看起来好像我其实是瞄准了可以使用LEFT OUTER JOIN函数来完成：
It looks as though what I was actually aiming for can be done using the LEFT OUTER JOIN function:
SELECT
MP_ROLES.MP_NUM,
MP_ROLES.ROLE_1,
MP_ROLES.ROLE_2,
MP_ROLES.ROLE_3,
MP_ROLES.ROLE_4,
ROLE1.ROLE_NUM, ROLE1.ROLE_DESCRIPTION AS POSITION1,
ROLE2.ROLE_NUM, ROLE2.ROLE_DESCRIPTION AS POSITION2,
ROLE3.ROLE_NUM, ROLE3.ROLE_DESCRIPTION AS POSITION3,
ROLE4.ROLE_NUM, ROLE4.ROLE_DESCRIPTION AS POSITION4
FROM MP_ROLES
LEFT OUTER JOIN ROLE AS ROLE1 ON MP_ROLES.ROLE_1 = ROLE1.ROLE_NUM
LEFT OUTER JOIN ROLE AS ROLE2 ON MP_ROLES.ROLE_2 = ROLE1.ROLE_NUM
LEFT OUTER JOIN ROLE AS ROLE3 ON MP_ROLES.ROLE_3 = ROLE1.ROLE_NUM
LEFT OUTER JOIN ROLE AS ROLE4 ON MP_ROLES.ROLE_4 = ROLE1.ROLE_NUM
WHERE (MP_ROLES.MP_NUM = @MP_NUM)
这篇关于表中的多个列的SQL连接的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！
