Hi Craig,
Sorry this is such a slow response but I have been swamped and I didn't
see where anyone else has responded yet.
First, we need to calculate the average ID per user but round the average
to the nearest whole number.
CREATE TEMPORARY TABLE tmpResults
SELECT cast((AVG(id)+ .5) as integer) as average,u.user_id, u.username
FROM users u
INNER JOIN routes rt
ON u.user_id = rt.user_id
INNER JOIN ranking rnk
ON rnk.rating = rt.rating
WHERE username='$username'
GROUP BY u.user_ID, username
Now, maybe we can give you the results you wanted.
SELECT rnk.rating as user_avg, tr.username, tr.user_id
FROM tmpResults tr
INNER JOIN ranking rnk
ON tr.average = rnk.id
OR if you wanted to see all of the Routes with the average information and
user information all in the same query
SELECT rnk.rating as user_avg, tr.username, tr.user_id, r.rating, r.route
FROM tmpResults tr
INNER JOIN ranking rnk
ON tr.average = rnk.id
INNER JOIN routes r
ON r.user_id = tr.user_id
As always, we need to clean up after ourselves:
DROP TABLE tmpResults
Hope this helped...
Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine
Craig Hoffman <[EMAIL PROTECTED]> wrote on 08/24/2004 09:24:55 PM:
> Hey Everyone,
> I can you some assistance on this query.
>
> I have three tables one is called ranking and the other is called
> routes and finally the users table. The users table is not really
> important.
> The ranking table looks like this:
>
> id rating
> 1 5.0
> 2 5.1
> 3 5.2
> 4 5.3
> 5 5.3a
> 6 5.3b
> and so on...
>
> The routes table looks like this:
>
> user_id route rating
> 1 somename 5.2
> 1 " " 5.3
> 1 " " 5.3a
>
> Here's my query:
> SELECT ranking.rating, AVG(id), users.username, users.user_id,
> routes.rating, routes.user_id FROM ranking, routes, users WHERE
> username='$username' AND users.user_id = routes.user_id AND
> ranking.rating = routes.rating GROUP BY username
>
> What I am trying to do is find the average rating for this user. For
> example:
>
> 5.2 = 3
> 5.3 = 4
> 5.3a = 5
> _______________
> 3 + 4 + 5 = 12 / 3 = 4
>
> So 4 = 5.3
> The average for this user would be 5.3. Any help would be most
> appreciated.
> Craig
>
>
> --
> MySQL General Mailing List
> For list archives: http://lists.mysql.com/mysql
> To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
>
