on 2/28/03 3:15 PM, Daniel Harik at lists@dharik.com appended the following bits to my mbox: > Thank You for your reply, but the problem is that users may have many > photos, and i need to get only one, i use folllowing sql: > SELECT users.username, photos.file FROM users left join photos on > users.id=photos.userid > > And i get: > > username file > dan 9a2de085e456e78ed66f079572638ff3.jpg > dan 852d28e6fa730f6d29d69aacd1059ae7.jpg > dan 672df2f16e89e3dc92ff74e3a0fa4b4f.jpg > dan 8bae6f20ed6e12ba1c86d04b8ebc9e1f.jpg > dan 7de9d2db2b2096cfc3f072f8c15a9e50.jpg > 404 f474a8ee5965f0a792e5b626fb30c2cd.jpg > 404 3acd391cf7abafa032c5e3b21eb7b322.jpg > 404 4e5df8cfa4bce5dd30c1166b8a86fa23.jpg > Bedman NULL > > but i want only 3 users from this join, not 3x3=9 So you just want the users who have pictures, but not all the pictures for each? Something like: SELECT count(*) AS num_photos, username FROM photos LEFT JOIN users ON photos.userid=users.id GROUP BY userid You could add the file field in there as well, but it would only be returning one of the files (the first or last one for that user, but I don't know of a way for you to be specific). Hope that helps. Sincerely, Paul Burney <http://paulburney.com/> Q: Tired of creating admin interfaces to your MySQL web applications? A: Use MySTRI instead. Version 3.1 now available. <http://mystri.sourceforge.net/> -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
