Help with SQL statement (JOIN)

I'm having some trouble with an SQL statement that have to find the number of students attending a course. My Database design look likes this:

Table Course: id | course_name

Table Student: id | name

And to connect the two many-to-many relationship I've an table:

Table course_student: id | course_id | student_id

What I want is to find out how many students are attending the course named "Database Design". I know that the ID is "1" but let’s 开发者_开发知识库say that I didn't knew, how would my SQL statement look like?

I have tried several different statements with different joins to first select the correct ID from the course table, where the name is "Database Design" and next I've to search in my course_student table where the course_id equal the founded id (in this case 1) and where all student_id is connected to this id.

I know it is a bit complex description so please tell me if I have to explain it in a better way.

Thanks Mestika

You can try something like

SELECT  COUNT(cs.student_id)
FROM    Course c INNER JOIN
        course_student cs ON c.id = cs.course_id
WHERE   c.course_name = 'Database Design'

You dont have to join to the Students table as you already have the ID in the course_student table, so 1 less join.

just a slightly different style, but same results

select COUNT(cs.student_id) as counter from Course c, course_student cs where c.id = cs.course_id and c.course_name = 'Database Design'

SELECT count(a.id)
FROM Course a
INNER JOIN Course_Student b
ON a.id = b.course_id
WHERE a.course_name = 'Database Design'