I'm attempting to pull-up some data.

This is actually the setup:

A [school] might have multiple [semester]. Just one [semester] could be active per [school].

The simplified fields per table:

  • [school] has 'id','title'
  • [school_semester] has 'id','school_id' (fk), 'semester_id' (fk), 'active', 'start_date', 'end_date'
  • [semester] has 'id', 'title'

The "school_semester" table holds information for it's specific school/semester. (startdate,enddate,etc).

Anyway, I'm simply attempting to pull-up all schools, as well as in this question I wish to also observe that school's current active semester.

Here's my query to date:

SELECT *, `school`.`name` as school_name
FROM (`school`)
LEFT JOIN `school_semester` ON `school`.`id` = `school_semester`.`school_id`
LEFT JOIN `semester` ON `semester`.`id` = `school_semester`.`semester_id`
ORDER BY `school_semester`.`active`

The issue:

This works if your school has semester that's active, but when it doesn't, it'll show the one that might be marked as inactive. Basically add the statement WHERE school_semester.active = 1, it excludes schools that don't come with an active semester..

Any pointers?

slowly move the condition around the joining :

SELECT *, `school`.`name` as school_name
FROM (`school`)
LEFT JOIN `school_semester` ON 
        `school`.`id` = `school_semester`.`school_id` AND
        `school_semester`.`active`
LEFT JOIN `semester` ON `semester`.`id` = `school_semester`.`semester_id`

By doing this, just the active semesters is going to be became a member of towards the result.

Add active=1 in your join condition.

SELECT *, `school`.`name` as school_name
FROM (`school`)
LEFT JOIN `school_semester` ON `school`.`id` = `school_semester`.`school_id`
    AND `school_semester`.`active` = 1
LEFT JOIN `semester` ON `semester`.`id` = `school_semester`.`semester_id`
ORDER BY `school_semester`.`active`