Help with a fairly simple MySQL Query

Question

I am trying to pull up some data.

Here is the setup:

A [school] can have multiple [semester]. Only one [semester] can 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 am simply trying to pull up all schools, and in this query I want to also see that school's current active semester.

Here is my query so far:

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 problem:

This works if a school has semester that is active, but if it does not, it will show one which may be marked as inactive. If I add the statement WHERE school_semester.active = 1, it excludes schools that do not have an active semester..

Any pointers?

krtek · Accepted Answer · 2011-03-09 20:18:37Z

2

move the condition on 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`

This way, only the active semesters will be joined to the result.

answered Mar 9, 2011 at 20:18

krtek

26.7k5 gold badges60 silver badges84 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Roeland Over a year ago

i think Joe's answer is pretty much the same as yours, but does "AND school_semester.active" mean the same thing as "AND school_semester.active = 1" ?

krtek Over a year ago

There's a good chance the statement will be equals. But since active is probably a boolean, there's no need to compare it like this.

Joe Stefanelli · Accepted Answer · 2011-03-09 20:19:34Z

0

Add active=1 as part of 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`

answered Mar 9, 2011 at 20:19

Joe Stefanelli

136k21 gold badges244 silver badges242 bronze badges

1 Comment

Roeland Over a year ago

didnt know you could do this inside the join statement :) you learn every day

Collectives™ on Stack Overflow

Help with a fairly simple MySQL Query

2 Answers 2

2 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related