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:

  if(u.country='gm',25,0) +
  if(u.age='4',25,0) +
  if(u.sex='2',25,0) +
  AS ranking

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

Thanx for any help guys,


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

Reply via email to