Re: stmt should return user-id only once. difficult select statement.

I agree, you are right, but that does not take me closer to the solution. So if I leave out the language_id in the group by statement I do get only the first language_id counted. Example: language table: language_id | user_id hr 20 en 20 gm 20 The statement would return only if I search for language hr, because it is the first it finds. If I search for en I do get nothing. Maybe I can't see what you mean. Please correct me, it sounds wired to me as well Andy "Ryan Jameson" < schrieb im Newsbeitrag news:CC8578B824E74940BA5126774F49628D0F9D83@NATHAN.ibs.org... That's only when they have a different language_id... the group by needs to have just user_id if you want to guarantee one user_id. GROUP BY u.user_id, l.language_id -----Original Message----- From: andy [mailto:news.letters@gmx.de] Sent: Friday, May 10, 2002 12:07 PM To: php-db@lists.php.net Subject: Re: [PHP-DB] stmt should return user-id only once. difficult select statement. unfortunatelly not. I did already try this. It does not make a difference. Still three results for each user do you have another idea? thanks, Andy "Ryan Jameson" <RJameson@usa.ibs.org> schrieb im Newsbeitrag news:CC8578B824E74940BA5126774F49628D1365EA@NATHAN.ibs.org... This may only be preference but you've mixed join syntaxes here, it would be easier to read if you stuck with just one. Anyway, I think what you'll want is to max the whole expression. So, keep the group by, but use max (if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0)) as ranking ........that should work. <>< Ryan ------------------------- SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking > 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 -----Original Message----- From: andy [mailto:news.letters@gmx.de] Sent: Friday, May 10, 2002 10:54 AM To: php-db@lists.php.net Subject: [PHP-DB] stmt should return user-id only once. difficult select statement. Hi there, I have a huge statement doing a calculation and returning a userid with a ranking. This workes fine for one table. But due to normalisation I do have 2. table containing languages of the users. Up to 3 languages for each user. The problem is, that I do get the same user 3 times with a different ranking. How can I get rid of the 2 lower ones and just keep the one with the highest ranking? I tryed around with max, but did not have any success. If I do leave only :GROUP BY u.user_id it returns only this user if the first language scores.So ... where to now?? Here is a sniplet of my statement: SELECT DISTINCT u.userid if(u.country='gm',25,0) + if(u.age='4',25,0) + if(u.sex='2',25,0) + if(l.language_id='en',25,0) AS ranking FROM data.user u, data.user_languages l, INNER JOIN db.countries AS c ON c.country_code = u.country /* countries */ LEFT JOIN db.cities AS ct ON ct.ID = u.city /* cities */ WHERE u.user_id = l.user_id /* because it is a different table */ AND u.country = 'gm' AND u.age = '4' AND u.sex = '2' /* for the mandatory fields */ GROUP BY u.user_id, l.language_id HAVING ranking > 0 /* dont return all users! */ ORDER BY ranking desc LIMIT 0,5 Thanx for any help guys, Andy -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php