SQL three tables to return one row

问题:

Hey people sorry if this question isnt really clean but it goes as following

i have a homework quest which goes along the lines of this. Write a quer to return how many subjects this student takes with the LastName "Field"

the three tables are:

Students:
     StudentID
     FirstName
     LastName

StudentCourses:
      StudentCourseID
      StudentID
      CourseID

Courses:
      Maths
      Science
      English
      P.E
      Drama
      Film Studies

I have something like

SELECT Students.studentID, 
       Students.Lastname, 
       Students.CourseID 
  FROM Students 
 WHERE Students.LastName = "Field"
 INNER JOIN StudentCourses 
    ON Student.CourseID = StudentCourses.CourseID
  FULL JOIN Student 
    ON Student.RoleID = Courses.CourseID

This is written so i cannot test it but does this seem correct to anyone?

回答1:

Use COUNT() function to find the number of courses the student takes and use WHERE clause to filter the student’s last name as ‘Field’.

SELECT s.StudentID, s.LastName, COUNT(sc.CourseID)
FROM Students s 
JOIN StudentCourses sc 
ON s.StudentID = sc.StudentID 
WHERE  s.LastName = 'Field’;

回答2:

Where should come last. You don't really need the 3rd join to the courses table because you aren't selecting anything from it.

SELECT st.STUDENTID, 
       st.LASTNAME, 
       count(stc.COURSEID) as COURSECOUNT
FROM   STUDENTS st 
       INNER JOIN STUDENTCOURSES stc 
               ON st.COURSEID = stc.COURSEID 
WHERE  st.LASTNAME = 'Field' 

回答3:

You can try the following query. WHERE clause should be after FROM and JOIN clauses

SELECT s.studentid,
       s.lastname,
       COUNT(sc.courseid)
  FROM student s
  JOIN studentcourses sc
    ON s.studentid = sc.studentid
 WHERE s.lastname = 'Field'