Query linking 4 tables with INNER JOIN

Question

I have the following 4 tables:

student_info:

S_ID naam   email   telefoon locatie U_ID
   1 Walter Walter@ 03938    Home       1

student_combi:

S_ID V_ID
   1    1

student_vak:

V_ID vak  R_ID
   1 HTML    1

student_richting:

R_ID richting 1 Web-Development

I would like the query to SELECT 'vak' (from the 'student_vak' table) and 'richting' (from the 'student_richting' table) using INNER JOIN

So far I have this:

SELECT student_vak.vak,student_richting.richting
FROM student_vak
INNER JOIN student_richting ON student_vak.R_ID = student_richting.R_ID
INNER JOIN student_combi ON student_info.S_ID = student_combi.S_ID
INNER JOIN student_vak ON student_combi.V_ID = student_vak.V_ID
INNER JOIN student_richting ON student_vak.R_ID = student_richting.R_ID

Thank you in advance if you know the answer.

Answer 1

This following will fulfil your need.

SELECT S.S_Id Student_Id, S.Naam Student_Name, SV.vak, SR.richting
FROM Student_Info S 
INNER JOIN Student_Combi SC ON S.S_id  = SC.S_Id
INNER JOIN Student_Vak SV ON SC.V_Id = SV.V_Id
INNER JOIN student_richting SR ON SV.R_Id = SR.R_Id

--Example

CREATE TABLE #student_vak
(V_ID   INT,
 vak        NVARCHAR(256),
 R_ID   INT)

INSERT INTO #student_vak
SELECT '1', 'HTML','1'

CREATE TABLE #student_richting
(R_ID       INT,
richting    NVARCHAR(256)
)
INSERT INTO #student_richting
SELECT '1', 'Web-Development'

SELECT SV.vak, SR.richting
FROM #student_vak SV
INNER JOIN #student_richting SR ON SV.R_Id = SR.R_Id