Query three tables with join

Posted on

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;

enter image description here

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

enter image description here

Answer :

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)

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

Leave a Reply

Your email address will not be published.