Query question

[Doug Matthews]

Hi. I have a table "info":

id, int, index
path, varchar(200)

and a table "media":

fileid, int, index
parent, int (relates to id in info)
filename, varchar(80)
added, datetime

I made a media catalog intranet site to keep track of all the stuff I watch. I wanted to have a "recently added media" page at the beginning. Originally, I did:

SELECT id,path,filename,added FROM media m INNER JOIN info i ON m.parent = i.id ORDER BY fileid DESC LIMIT 10

This worked fine. However, I noticed that when I imported a whole season of something it totally blew everything else off the recent list so I wanted to change it to "recently updated folders". I did this first:

SELECT DISTINCT(path) FROM media m INNER JOIN info i ON m.parent = i.id ORDER BY fileid DESC LIMIT 10

It worked great. I then wanted to have the date of the last file added in the results, and that's where the wheels came off my wagon lol. I would need the info field "path" to show what folder was updated and the field "id" to use to get the media records so I did:

SELECT DISTINCT(id) AS newid,(SELECT path FROM info WHERE id = newid) AS path FROM media m INNER JOIN info i ON m.parent = i.id ORDER BY fileid DESC LIMIT 10

That worked fine so I took it one step further:

SELECT DISTINCT(id) AS newid,(SELECT path FROM info WHERE id = newid) AS path,(SELECT added FROM media WHERE parent = newid ORDER BY fileid DESC LIMIT 1) AS added FROM media m INNER JOIN info i ON m.parent = i.id ORDER BY fileid DESC LIMIT 10

This just hangs. I let the query work for like 15 seconds before I aborted it.

What would be the right way to get back the path from info and the latest file added value from media for said info record? Thanks!