Re: Making Join

Paul Burney wrote: > 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, > Thank You, group by users.id did the trick, but now i have another problem, i want to select users with no photo as well, all with same sql statment, so far i have SELECT users.id, users.gender, users.year, users.month, users.day, users.username, users.city, users.country, users.feet, users.inches, users.cm, users.openingLine, profiles.bodyType, profiles.ethnic, profiles.smoke, profiles.drink, profiles.children, profiles.religion, profiles.moment, photos.file FROM users,profiles, photos WHERE users.id=profiles.userid GROUP BY users.id It works fine selecting random photo for user, but doesn't select users with no photos.