CODEWITHSUNDEEP
sql-serverjoin
Ashok
Ashok

Reputation: 1906

JOIN query to retrieve multiple column referencing to a single column from another table

Below is my database diagram which includes three tables and their relations - PersonalInfo,Subjects,Students

Database diagram

Currently, Students table have below values.

Current data in Students table

I'm trying to query data to be displayed as below.

Expected output

What I tried

SELECT FullName, SubjectName AS 'Subject1', SubjectName AS 'Subject2',
SubjectName AS 'Subject3', SubjectName AS 'Subject4',SubjectName AS 'Subject5' 
FROM Subjects JOIN Students ON Students.Subject1 =Subjects.Id 
JOIN PersonalInfo ON PersonalInfo.Id=Students.StudId

Getting below output which is wrong. It's showing only name of the first subject.

Output

Could anyone guide me the right way.

Upvotes: 1

Views: 46

Answers (2)

Ashok
Ashok

Reputation: 1906

Working query 

SELECT FullName, 
sb.SubjectName AS 'Subject1', 
sb2.SubjectName AS 'Subject2',
sb3.SubjectName AS 'Subject3', 
sb4.SubjectName AS 'Subject4',
sb5.SubjectName AS 'Subject5' 
FROM Students s 
JOIN PersonalInfo ON PersonalInfo.Id=s.StudId
JOIN Subjects sb ON s.Subject1 =sb.Id 
JOIN Subjects sb2 ON s.Subject2 =sb2.Id 
JOIN Subjects sb3 ON s.Subject3 =sb3.Id 
JOIN Subjects sb4 ON s.Subject4 =sb4.Id
JOIN Subjects sb5 ON s.Subject5 =sb5.Id

Result

Output

Upvotes: 1

Vishal Kottarathil
Vishal Kottarathil

Reputation: 346

You have to join multiple time for each subjects. There is a confusion in 'Sudent' and 'PersonalInfo' table ids and Foreign keys. So please join it according to the design. Below query will give you all the subject names as you required. Please consider the query as a logic. IT may contain syntax error as I typed it here

Select SUB1.SubejctName as Subject1,SUB2.SubejctName as Subject2,SUB3.SubejctName as Subject3,SUB4.SubejctName as Subject4,SUB5.SubejctName as Subject5, from Students S 
    inner join Subjects SUB1 on SUB1.id=S.Subject1
    inner join Subjects SUB2 on SUB2.id=S.Subject2
    inner join Subjects SUB3 on SUB3.Id=S.Subject3
    inner join Subjects SUB4 on SUB4.id=S.Subject4
    inner join Subjects SUB5 on SUB5.Id=S.Subject5

Todo - Join with PersonalInfo table to show student name.

Upvotes: 4

Related Questions