Re: [PHP-DB] Making Join
Mark wrote: --- Daniel Harik [EMAIL PROTECTED] wrote: Hello, I have 2 tables users table, that stores user info, and photos table, i want to select all users with one photo form photos table, but i don't want it to be photos * users = result. Is there way to do it? Assuming you mean, you have a table called user_info and another called photos and each of them has a common element AND there must be a photo for the user then you could use this sort of select... select user_info.name, photo.image from user_info, photo where user_info.id = photo.user_id The above will not display anything if there are no matching id to user_id so if one of your users didn't have a photo they would not get displayed. Also, if a user had more than one photo they would all be displayed. If your users are expected to have one and only one photo then you may want get rid of the photo table and just put the photo in the user table. HTH 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 If you only want the users, and not the photos, then the previous post should do what you want. But if you also want the photo, and there's more than one photoo for a user, how will the code know which photo you want? Do you want a random photo from each user? The last photo from each user? The first? Mark Random is fine -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Making Join
Paul Burney wrote: on 2/28/03 3:15 PM, Daniel Harik at [EMAIL PROTECTED] 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. -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
[PHP-DB] Making Join
Hello, I have 2 tables users table, that stores user info, and photos table, i want to select all users with one photo form photos table, but i don't want it to be photos * users = result. Is there way to do it? Assuming you mean, you have a table called user_info and another called photos and each of them has a common element AND there must be a photo for the user then you could use this sort of select... select user_info.name, photo.image from user_info, photo where user_info.id = photo.user_id The above will not display anything if there are no matching id to user_id so if one of your users didn't have a photo they would not get displayed. Also, if a user had more than one photo they would all be displayed. If your users are expected to have one and only one photo then you may want get rid of the photo table and just put the photo in the user table. HTH 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
Re: [PHP-DB] Making Join
on 2/28/03 3:15 PM, Daniel Harik at [EMAIL PROTECTED] 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
Re: [PHP-DB] Making Join
--- Daniel Harik [EMAIL PROTECTED] wrote: Hello, I have 2 tables users table, that stores user info, and photos table, i want to select all users with one photo form photos table, but i don't want it to be photos * users = result. Is there way to do it? Assuming you mean, you have a table called user_info and another called photos and each of them has a common element AND there must be a photo for the user then you could use this sort of select... select user_info.name, photo.image from user_info, photo where user_info.id = photo.user_id The above will not display anything if there are no matching id to user_id so if one of your users didn't have a photo they would not get displayed. Also, if a user had more than one photo they would all be displayed. If your users are expected to have one and only one photo then you may want get rid of the photo table and just put the photo in the user table. HTH 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 If you only want the users, and not the photos, then the previous post should do what you want. But if you also want the photo, and there's more than one photoo for a user, how will the code know which photo you want? Do you want a random photo from each user? The last photo from each user? The first? Mark = Mark Weinstock [EMAIL PROTECTED] *** You can't demand something as a right unless you are willing to fight to death to defend everyone else's right to the same thing. *** __ Do you Yahoo!? Yahoo! Tax Center - forms, calculators, tips, more http://taxes.yahoo.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
