Mysql queries issue

I have 3 tables in my mysql DB to query.

• Users_rates (fields: id,userid,raterid,rate,eventid) containing all of the rates(rate) that have been assigned to users(userid), participating to specific events(eventid), by other users(raterid)

• Events_participants (fields:id,userid,eventid) containing all of the users(userid) participating to each event(eventid)

• Users (fields:id,name,lastname)containing all the user relative data

I need to query those three tables to retrieve an event-specific rank for the users' rates.

Ex. John,Erik and Mark participated to 'eventid=31'. John received 1 rate from Mark, and 2 from Erik. Mark received 1 rate from Erik. Nobody has rated Erik though.

I need to retrieve for each user name,lastname and the sum of the rates received for eventid=31

I tried with this:

SELECT events_participants.userid,users.name,users.lastname,
(SELECT SUM(rate)FROM users_ra开发者_如何学运维tes WHERE users_rates.eventid=31 AND users_rates.userid=events_participants.userid)AS rate 
FROM(( events_participants INNER JOIN users ON events_participants.userid=users.id)
LEFT OUTER JOIN users_rates ON events_participants.userid=users_rates.userid )
WHERE events_participants.eventid=31

But I receive:

userid | name | lastname | rate  

1 | luca | silvestro | 1 

3 | claudio | buricchi | 6

3 | claudio | buricchi | 6

What's the right query?

Thanks

Luca

---

Try this:

SELECT users.userid, users.name, users.lastname, temp.sum as rate
FROM users LEFT JOIN (
    SELECT userid, SUM(rate) as sum FROM users_rates WHERE eventid = 31 GROUP BY userid
) as temp USING (userid)

It might give an error, this might work instead:

SELECT users.userid, users.name, users.lastname, temp.sum as rate
FROM users, (
    SELECT userid, SUM(rate) as sum FROM users_rates WHERE eventid = 31 GROUP BY userid
) as temp WHERE users.userid = temp.userid

---

I don't know if I got the problem right, but maybe something like:

SELECT u.id, u.name, u.lastname, SUM(ur.rate) AS rate
FROM users AS u
INNER JOIN users_rates AS ur ON ur.userid = u.id
WHERE ur.eventid = 31
GROUP BY u.id

edit: If you want to receive a list with all users regardless of whether they have any rates at all, you could also join the users_participants table and replace the INNER JOIN of users_rates by a LEFT JOIN. The WHERE clause has to reference events_participants then (not users_rates anymore as it could be NULL):

SELECT u.id, u.name, u.lastname, SUM(ur.rate) AS rate
FROM users AS u
INNER JOIN events_participants AS ep ON ep.userid = u.id
LEFT JOIN users_rates AS ur ON ur.userid = u.id AND ur.eventid = ep.eventid
WHERE ep.eventid = 31
GROUP BY u.id