sql - 根据主表(TABLE)中的外键，SELECT 外键表(TABLE B)中的非 ID 列

Question

我有两个表，一个是 STUDENT 表，一个是 RULES 表。学生表包含与学生相关的数据，规则表包含与特定学生相关的规则。现在在学生表上，我有 5 列作为规则表的外键，例如

CREATE TABLE dbo.RULES
(
    ID int identity not null primary key,
    RULENAME varchar
)

CREATE TABLE dbo.STUDENT
(
    ID int identity not null primary key,
    NAME varchar(50),
    SURNAME varchar(50),
    ADRESS varchar(50),
    RULE1 int not null references dbo.RULES(ID),
    RULE2 int not null references dbo.RULES(ID),
    RULE3 int not null references dbo.RULES(ID),
    RULE4 int not null references dbo.RULES(ID),
    RULE5 int not null references dbo.RULES(ID)
)

我想要实现的是从学生表中选择 NAME、SURNAME、ADDRESS，并将其与 STUDENT 中每个 RULE 外键的 RULENAME 连接起来，例如

NAME SURNAME ADDRESS RULE1NAME RULE2NAME RULE3NAME RULE4NAME RULE5NAME

我整晚都在努力破解这个问题，但遗憾的是我仍然处于第一阶段。我将其归因于我缺乏 SQL 经验，但是，是的，有漂亮的人可以帮助我吗？

更新

感谢你们的回答，dotnetom，Bharadwaj。

我按照 dotnetoms 回答中的建议内部加入了 5 次。查询工作正常，但它为所有五个规则返回相同的规则。这是我的查询

SELECT 
STUDENT.NAME, 
STUDENT.SURNAME, 
STUDENT.ADDRESS, 
RULES.RULENAME AS RULE1NAME, 
RULES.RULENAME AS RULE2NAME, 
RULES.RULENAME AS RULE3NAME, 
RULES.RULENAME AS RULE4NAME, 
RULES.RULENAME AS RULE5NAME
FROM STUDENT s 
INNER JOIN RULES AS r1 ON STUDENT.RULE1 = RULES.ID
INNER JOIN RULES AS r2 ON STUDENT.RULE2 = RULES.ID
INNER JOIN RULES AS r3 ON STUDENT.RULE3 = RULES.ID
INNER JOIN RULES AS r4 ON STUDENT.RULE4 = RULES.ID
INNER JOIN RULES AS r5 ON STUDENT.RULE5 = RULES.ID

有什么建议吗？谢谢!

Answer 1

您应该能够使用此查询:

SELECT 
    s.NAME, 
    s.SURNAME, 
    s.ADDRESS, 
    r1.RULENAME AS RULE1NAME, 
    r2.RULENAME AS RULE2NAME, 
    r3.RULENAME AS RULE3NAME, 
    r4.RULENAME AS RULE4NAME, 
    r5.RULENAME AS RULE5NAME
FROM STUDENT s 
    INNER JOIN RULES r1 ON s.RULE1 = r1.ID
    INNER JOIN RULES r2 ON s.RULE2 = r2.ID
    INNER JOIN RULES r3 ON s.RULE3 = r3.ID
    INNER JOIN RULES r4 ON s.RULE4 = r4.ID
    INNER JOIN RULES r5 ON s.RULE5 = r5.ID