RE: [PHP-DB] stmt should return user-id only once. difficult select statement.

I may be confused as to what you're looking for, however I can tell you that the group by is done after the result set is created, hence the where clause is processed. So, if you search for where u.country = 'hr' then you will only ever get 'hr' in the country field after the group by is processed. So if you mean by saying "search for language hr" that you put that criteria in your where clause then it means there is no row where the criteria is met. If you're hope is to be able to search the result of the first query for languages then you will have to have multiple rows per user id, one for each country. <>< Ryan -----Original Message----- From: Andy [mailto:news.letters@gmx.de] Sent: Friday, May 10, 2002 4:10 PM To: php-db@lists.php.net Subject: Re: [PHP-DB] 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php