Left Join with Group By

Question

I am using PostgreSQL 9.4.

I have a table of workouts. Users can create multiple results for each workout, and a result has a score.

Given a list of workout_ids and two user_ids, I want to return the best score for each workout for each user. If the user does not have a result for that workout, I want to return a padded/null result.

SELECT "results".*, "workouts".* 
FROM "results" LEFT JOIN "workouts" ON "workouts"."id" = "results"."workout_id" 
WHERE (
  (user_id, workout_id, score) IN 
  (SELECT user_id, workout_id, MAX(score) 
    FROM results WHERE user_id IN (1, 2) AND workout_id IN (1, 2, 3) 
    GROUP BY user_id, workout_id)
) 

In this query, the left join is acting as an inner join; I'm not getting any padding if the user has not got a result for the workout. This query should always return six rows, regardless of how many results exist.

Example data:

results
user_id | workout_id | score 
-----------------------------
      1 |          1 |     10
      1 |          3 |     10
      1 |          3 |     15
      2 |          1 |      5

Desired result:

results.user_id | results.workout_id | max(results.score) | workouts.name
-------------------------------------------------------------------------
              1 |                  1 |                 10 | Squat
              1 |                  2 |               null | Bench
              1 |                  3 |                 15 | Deadlift
              2 |                  1 |                  5 | Squat
              2 |                  2 |               null | Bench
              2 |                  3 |               null | Deadlift

Answer 1

The where filters out your NULL values, so that is why the result is not what you expect.

Joinint the WHERE clause results instead of filter the where clause results.

SELECT "results".*, "workouts".*,"max_score".*
FROM "results" 
LEFT JOIN "workouts" ON "workouts"."id" = "results"."workout_id"
LEFT JOIN (SELECT user_id, workout_id, MAX(score) 
    FROM results WHERE user_id IN (1, 2) AND workout_id IN (1, 2, 3) 
    GROUP BY user_id, workout_id) max_score ON workouts.workout_id=max_score.workout_id;

You need to alter the SELECT to get the correct columns.