Re: Making Join

Mark wrote: > > --- Daniel Harik <lists@dharik.com> 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