Query three tables with join

Question

I have five tables that are connected with each other using joins:

• department vs course -- one to many
• course vs classes -- one to many
• classes vs section -- many to many

I want to retrieve the sections the belong to a particular course. I have tried inner join to achieve this but I can not get good results, I get all the sections instead.

select sectionname from Section 
inner join Classes on classid=sectionid 
inner join Course on courseid=Classes.fk_course_courseid;

Data contained in the table as shown below. I want to retrieve all the sections the belong to courseid = 1 or department id = 1.

Answer 1

i think there should be separate table for class,course relation, Class_Course_Mapping

Check this (Untested),

select sectionname,sectionid
from Section
where exists(
SELECT fk_course_courseid 
FROM    course  
       INNER JOIN classes   
              ON courseid = fk_course_courseid 
INNER JOIN classsection 
ON classid = classsection.fk_class_classid                
WHERE  coursename = 'School' and Section.sectionid=classsection.fk_section_sectionid)

Answer 2

SELECT DISTINCT sectionname , 
               sectionid 
FROM   section 
       LEFT JOIN classsection 
              ON classsection.fk_section_sectionid = section.sectionid 
       LEFT JOIN classes 
              ON classid = classsection.fk_class_classid 
       LEFT JOIN course 
              ON courseid = fk_course_courseid 
WHERE  coursename = 'School'

i just joined section and classses first and then joined courses to see the department name which sections are belong to which classes and it worked kudos