0

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

  • Could you please put your table definitions as standard SQL DDL (CREATE TABLE blah (field_1 type_1,...); and your data as standard SQL DML (INSERT INTO blah VALUES (...);). Either provide it here or in the form of a fiddle - that would be great. Help us to help you! :-) My profile has a few articles on how to ask questions here - you might want to look? – Vérace May 18 at 12:25
  • Based on the info you've provided, there is no link between Section and Course - there is a link from Section to Department and Course to Department so at best you could get all the sections for all the courses in a given department, but there is no relationship defined for section to course so no way to join and retrieve this information. – HandyD May 20 at 1:10
  • yah no link between section and course but the link is through department – ahmedshah May 20 at 9:08
0

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)
0
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

  • DISTINCT is not a function and doesn't need parentheses. – ypercubeᵀᴹ May 22 at 9:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.