how to use select and count in sql for search columns in diffrent table-CodePudding

i have these tables :

course | course_id title dept_name(FK) credits

section | course_id(FK) sec_id semester year building(FK)

takes | ID course_id(FK) sec_id(FK) semester(FK) year(FK)

how to create SQL query that print course code, course name, and number of students taking the course held at Watson building ?

i can count just number of students for use select count(distinct id) from takes natural join section where section.building = 'Watson';

CodePudding user response：

I would try something like this

select course_id, titile, count(1) takes
from course c
inner join
section s on c.course_id = s.course_id and s.building = @building
left join
takes t on t.course_id = c.course_id
group by course_id, title