Help with a fairly simple MySQL Query
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 tha开发者_如何学JAVAt 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?
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.
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`
精彩评论