Mysql queries issue

Question

I have 3 tables in my mysql DB to query.

enter image description here

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_rates 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

Halcyon · Accepted Answer · 2011-04-25 10:02:12Z

1

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

answered Apr 25, 2011 at 10:02

Halcyon

57.7k10 gold badges93 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Flinsch · Accepted Answer · 2011-04-27 14:38:23Z

1

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

edited Apr 27, 2011 at 14:38

answered Apr 25, 2011 at 10:03

Flinsch

4,3591 gold badge23 silver badges29 bronze badges

2 Comments

luca Over a year ago

what if I need to retrieve a list of all of the users partecipating to the event even if they didnt receive yet a rate (so they're not in the users_rates table)??

Flinsch Over a year ago

Then you have to also join the events_participants table and replace the INNER JOIN by a LEFT JOIN. I'll edit my post in this direction.

Collectives™ on Stack Overflow

Mysql queries issue

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related