Tricky SQL when joining

Question

I've two tables, shows, and objects. I want to print out the latest objects, and the shownames for them. Right now I'm doing it this way:

SELECT MAX(objects.id) as max_id, shows.name, shows.id 
FROM shows, objects 
WHERE shows.id = objects.showId
GROUP BY shows.name 

However, if I also want to fetch the episode of the object I can't put it like SELECT object.episode [...], because then won't automatically select the object which is MAX(objects.id), so my question is how to do that?

If you haven't already figured out my tables they're like this:

• Shows
  • Id
  • Name

And also:

• Objects
  • Id
  • Name
  • Episode
  • Season
  • showId

Using MySQL.

Answer 1

Something like this (untested):

SELECT objects.id as max_id, objects.episode, shows.name, shows.id
  FROM shows, objects 
 WHERE shows.id = objects.showId
   AND objects.id = (
        SELECT MAX(id) FROM objects
         WHERE name = shows.name
       )