Re: [PHP-DB] Making Join

[Daniel Harik]

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

Re: [PHP-DB] Making Join

[Daniel Harik]

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

[Mark]

--- 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

Re: [PHP-DB] Making Join

[Paul Burney]

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


Q: Tired of creating admin interfaces to your MySQL web applications?

A: Use MySTRI instead. Version 3.1 now available.




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