You could try: SELECT users.Name, Files.Filename FROM Files LEFT JOIN users ON Files.Userid = users.Id GROUP BY Files.Userid ORDER BY Files.Stamp DESC
I'm not sure if this would work, depends on if MySQL ORDER's before GROUP. You could try it though. Capitolization may be incorrect, I tried to use your convention, but I may have missed something. Robert V. Zwink http://www.zwink.net/daid.php -----Original Message----- From: Michael Robbins [mailto:[EMAIL PROTECTED]] Sent: Friday, April 19, 2002 4:55 PM To: [EMAIL PROTECTED] Subject: [PHP-DB] Help with a JOIN statement I have 2 tables, 'users' and 'files' (defined below) Can I construct a single SELECT statement so that it will return ONLY users that have files and ONLY the last file that the user has? So it would return 1 row per user containing their most recent file. users -------- Id int Name varchar Files -------- Id int Userid int Filename varchar Stamp int (unix_timestamp) The way I'm doing it now, I use SELECT DISTINCT to get all the users with files, then a separate query for each user returned to retrieve their latest file. This method requires many, many individual queries to the database? Any help is appreciated. Thanks, Mikey -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
