Re: stmt should return user-id only once. difficult select statement.
| From: | andy | Date: | Fri, 10 May 2002 18:07:20 +0000 |
| Subject: | Re: stmt should return user-id only once. difficult select statement. | ||
| References: | 1 | Groups: | php.db |
| Request: | Send a blank email to php-db+get-19107@lists.php.net to get a copy of this message | ||
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