Re: Making Join

[Date Prev][Date Next][Thread Prev][Thread Next][Date Index][Thread Index]

 



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

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php


[Index of Archives]     [PHP Home]     [PHP Users]     [Postgresql Discussion]     [Kernel Newbies]     [Postgresql]     [Yosemite News]

  Powered by Linux