Left Join query with where clause

Question

I have 3 tables: Group, Person and Workout. Group is a foreign key to Person, and Person is a foreign key to Workout. I want to select the list of all the people in a certain group, and if there was a workout between the given dates for that person, then also get the duration, if not, just make it null.

Not quite sure how to write the SQL, I'm stuck in something like this:

SELECT Person.id, Person.firstName, Workout.duration 
FROM Person LEFT OUTER JOIN Workout 
ON Person.id = Workout.person_id 
WHERE Workout.start_date BETWEEN %s AND %s 
AND Person.group_id = %s

Any ideas?

Gordon Linoff · Accepted Answer · 2013-06-21 20:48:35Z

5

Your where clause is undoing the left join. When there is no match, start_date is NULL, which fails the comparison.

Moving the condition to the on clause fixes this:

SELECT Person.id, Person.firstName, Workout.duration 
FROM Person LEFT OUTER JOIN Workout 
     ON Person.id = Workout.person_id and Workout.start_date BETWEEN %s AND %s 
where Person.group_id = %s

answered Jun 21, 2013 at 20:48

Gordon Linoff

1.3m62 gold badges705 silver badges857 bronze badges

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

1 Comment

leonsas Over a year ago

Thanks, I knew it was something simple, yet I couldn't figure it out.

Collectives™ on Stack Overflow

Left Join query with where clause

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related