Is it possible to condense these queries into one?

Question

I have two tables, with one containing records referencing the other:

Goal
  id (int)
  name (text)
  value_mask (text)

GoalStatus
  id (int)
  goal (int)
  created (datetime)
  value (text)

Goal.id == GoalStatus.goal

What I'd like to do, is pull the latest record from the GoalStatus table, for each record in Goal. At the moment the only way I know how to do this is by making a separate query for each record in Goal (pseudocode):

goals = db.query("SELECT * FROM Goal")

foreach (goals as goal):
    goalStatus = db.query("
        SELECT * FROM GoalStatus
        WHERE goal = " + goal.id + "
        ORDER BY created DESC
        LIMIT 1
    ")

Is there a way of condensing this, so I'm not making an extra query for each Goal?

Answer 1

You could join on GoalStatus where there is no other goal status with the same goal id that has a greater created date.

SELECT * 
FROM Goal 
    INNER JOIN GoalStatus on Goal.GoalId=GoalStatus.GoalId 
WHERE Not Exists(SELECT * 
                 FROM GoalStatus innerGs 
                 WHERE innerGs.GoalId=Goal.GoalId 
                     and innerGs.created > GoalStatus.created)

Pretty ugly and will probably perform poorly but all I could think of.

Answer 2

select 
      g.*,
      gs.id GoalStatusID,
      gs.created,
      gs.value
   from 
      goal g inner join goalstatus gs
          on g.id = gs.goal
   where 
      gs.created in 
          ( select max( gs2.created )
               from goalstatus gs2
               where g.id = gs2.goal )

Answer 3

This is the per-group-maximum question. It's a very common thing to want to do that SQL doesn't make easy, so it gets asked a lot.

Here's a summary of approaches you can take. They'll have different performance attributes, and can behave differently when there are two rows sharing the same value as the maximum.

As a default first approach I would tend to go for a null-left-join rather than a subquery:

SELECT ...
FROM Goal
JOIN GoalStatus AS Gs0 ON Gs0.goal=Goal.id
LEFT JOIN GoalStatus AS Gs1 ON Gs1.goal=Goal.id AND Gs1.created>Gs0.created
WHERE Goal.id=(someid)
AND Gs1.id IS NULL

that is to say, join rows where there is no other row that has a greater created value.