SELECT a non ID column in a foreign key table (TABLE B) based on the foreign key in the primary table (TABLE

The Complex One Published at Dev

The Complex One

I have two tables, a STUDENT and RULES table. The student table holds data related to students and the rules table holds rules related to a particular student. Now on the student table, I have 5 columns as foreign keys to the rule table e.g

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

What I want to achieve is selecting NAME, SURNAME, ADDRESS from student table and join it with the RULENAME for each RULE foreign key in STUDENT e.g

NAME SURNAME ADDRESS RULE1NAME RULE2NAME RULE3NAME RULE4NAME RULE5NAME

I stayed up the whole night trying to crack this but sadly I'm still at square one. I will attribute that to my lack of experience in SQL but ya, can someone pretty pretty please help me?

UPDATE

Thanks for your answers guys, dotnetom, Bharadwaj.

I inner joined 5 times as suggested on dotnetoms answer. The query works fine but it returns the same rule for all five rules. Here's my query

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

any suggestions? Thanks!

dotnetom

You should be able to use this query:

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

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-10

Comments

0 comments

From Dev

Insert a new column into the Foreign Key table when a particular column in the primary key table is Update (Using Triggers)

From Dev

Update Column Of All Rows In A Table From Another Table Using Primary Key And Foreign Key

From Dev

How to display the last inserted ID (primary key) of a table in a html textbox that is in a relationship with a foreign key of another table in mvc

Related Related

Article