Complex SQL when joining

Question

I have two tables, shows and objects. I want to print the latest objects and names for them. Now I am doing it like this:

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 get an episode of an object, I cannot put it as SELECT object.episode [...], because then it will not automatically select the object that is MAX(objects.id), so my question is how to do it?

If you haven't figured out my tables yet, they will be like this:

• Show
  • ID
  • name

and:

• The objects
  • ID
  • name
  • episode
  • season
  • showId

Using MySQL. Thank!

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
       )

Answer 2

SELECT objects.id as max_id, shows.name, shows.id 
FROM shows, objects 
WHERE shows.id = objects.showId
ORDER BY objects.id DESC
GROUP BY shows.name 
LIMIT 1

Does it do what you need?

Answer 3

, - :

SELECT objects.id, objects.episode, shows.name, shows.id 
FROM shows 
JOIN objects AS objects_for_max ON shows.id = objects.showId
JOIN objects ON objects.id=MAX(objects_for_max.id)
GROUP BY shows.name