CODEWITHSUNDEEP
mysqlsqljoinsubquery
Ryan Ballantyne
Ryan Ballantyne

Reputation: 4094

Help with SQL join combined with lots of subqueries

I have limited experience with joins, and this puzzle has me stumped.

Here are the relevant tables from my mysql database:

My Goal: retrieve all students that are enrolled in the sections that have exams scheduled on a given date. Also get exam scheduling information for each test that the student has to take on the given date. It is desirable to have a row in the result set for each student<->exam_schedule pair.

I have a query that accomplishes the first half of my goal (it has a lot of subqueries):

SELECT * FROM `students` WHERE `id` IN
    (SELECT `student_id` FROM `sections_students` WHERE `section_id` IN
        (SELECT `section_id` FROM `sections_exams` WHERE `exam_id` IN
            (SELECT `exam_id` FROM `exam_schedules` WHERE `date` = DATE('$date') AND `exam_id` IN
                (SELECT `id` FROM `exams` WHERE `isAutoSignup` = 1))))

What I can't figure out is how to incorporate a join into that in order to accomplish the second half of my goal. My every attempt has produced a syntax error. Please, can anyone point me in the right direction? Thanks in advance!

Upvotes: 1

Views: 312

Answers (2)

Daniel Williams
Daniel Williams

Reputation: 9312

Here is a shot:

SELECT S.Student_name, E.Exam_name, ES.date
FROM students S
LEFT OUTER JOIN SECTIONS_STUDENTS SS
  ON SS.student_id = S.stuent_id
LEFT OUTER JOIN SECTIONS_EXAMS SE
  ON SE.section_id = SS.section_id
LEFT OUTER JOIN  EXAMS E
  ON E.exam_id = SE.exam_id
LEFT OUTER JOIN EXAM_SCHEDULES ES
  ON ES.exam_id =E.exam_id
WHERE 1=1
  AND E.isAutoSignup = 1
  AND ES.Date = DATE('$date')

Joins are like writing out your thoughts.

I want the students and I want students in sections and I want the exams for the sections and I want the schedules for the exams filter on exam schedules, and exams in this way...

Upvotes: 0

manji
manji

Reputation: 47978

you can do all that with joins and thus obtaining exam_schedules in the way:

SELECT s.*, es.*
  FROM students s
JOIN sections_students ss on s.id          = ss.student_id
JOIN sections_exams    se on se.section_id = ss.section_id
JOIN exam_schedules    es on es.exam_id    = se.exam_id and date = DATE('$date')
JOIN exams             e  on e.id          = es.exam_id and isAutoSignup = 1

Upvotes: 3

Related Questions