CODEWITHSUNDEEP
mysqlsql
Mohamad Arafat
Mohamad Arafat

Reputation: 573

Issue with mysql subquery

I am confused on how to make a sql query which will show student details who is taking subject that has been taught by a particular lecturer.

There is 5 table is my db.

Subject (subjectCode, subjectName, creditHour, studyMode)

Student (stdID,stdName`)

Lecturer (lecID,lecName`)

Lecturer-subject (lec_subID, lec_userID, subjectID)

Student-subject (std_subID, student_userID, subjectCode)

Upvotes: 0

Views: 48

Answers (3)

ZORRO_BLANCO
ZORRO_BLANCO

Reputation: 909

select Sturent.stdName as StudentName, 
    Subject.subjectName as SubjectName, 
    Lecturer.lecName as LecturerName, 
    Subject.creditHour as CreditHour,
    Subject.studyMode as StudyMode from Student
    join Student-subject on student.stdID= Student-subject.student_userID
    join Subject on Student-subject.subjectCode = subject.subjectCode
    join Lecturer-subject on Lecturer-subject.subjectID = subject.subjectCode
    join Lecturer on Lecturer-subject.lec_userID = lectrer.lecID

or 

select Sturent.stdName as StudentName, 
    Subject.subjectName as SubjectName, 
    Lecturer.lecName as LecturerName, 
    Subject.creditHour as CreditHour,
    Subject.studyMode as StudyMode from Student, Student-subject, 
Subject, Lecturer-subject, Lecturer 
where student.stdID= Student-subject.student_userID
    and Student-subject.subjectCode = subject.subjectCode
    and Lecturer-subject.subjectID = subject.subjectCode
    and Lecturer-subject.lec_userID = lectrer.lecID

Upvotes: 0

Mahesh
Mahesh

Reputation: 8892

You need to apply the join and WHERE clause to get the data you want.

SELECT DISTINCT 
    Sturent.stdName as StudentName, 
    Subject.subjectName as SubjectName, 
    Lecturer.lecName as LecturerName, 
    Subject.creditHour, 
    Subject.studyMode  
FROM 
  Student JOIN Student-subject 
   ON student.stdID= Student-subject.student_userID
  JOIN Subject 
   ON Student-subject.subjectCode = subject.subjectCode
  JOIN Lecturer-subject 
   ON Lecturer-subject.subjectID = subject.subjectCode
  JOIN Lecturer 
   ON Lecturer-subject.lec_userID = lectrer.lecID
 WHERE
    Lecturer.lecName = 'some lecturer name'

Upvotes: 1

Rohìt Jíndal
Rohìt Jíndal

Reputation: 27232

Try this it will work :

Use Inner Join

Select t1.`stdId`,t1.`stdName`,t2.`std_subID`,t3.`subjectName`,t3.`creditHour`,t3.`studyMode`,t4.`lec_subID`,t5.`lecName` from Student t1
JOIN Student-subject t2 ON t2.`student_userId`=t1.`stdID`
JOIN Subject t3 ON t3.`subjectCode`=t2.`subjectCode`
JOIN Lecturer-subject t4 ON t4.`subjectID`=t2.`std_subID`
JOIN Lecture t5 ON t5.`lecID`=t4.`lec_userID`

Upvotes: 0

Related Questions