CODEWITHSUNDEEP
mysqlsql
vchornenyi
vchornenyi

Reputation: 334

How to get rating for each user?

i have a such type of relations in db. db relations image

I wrote such SQL code to get list of user with their average rating.

SELECT
  c.id,l.login,
    AVG(f.mark) AS AVGRating
  FROM logininfo l
 INNER JOIN person p
   ON l.id = p.info_id
 JOIN candidate c
    ON p.id = c.id
  JOIN feedback f
GROUP BY c.id
ORDER BY AVGRating ASC;

Problem is that I get same rating for all users.

Upvotes: 0

Views: 72

Answers (2)

Vamsi Prabhala
Vamsi Prabhala

Reputation: 49260

SELECT
c.id,l.login,
AVG(f.mark) AS AVGRating
FROM logininfo l
INNER JOIN person p
ON l.id = p.info_id
JOIN candidate c
ON p.id = c.id
JOIN feedback f
--add a join condition
GROUP BY c.id
ORDER BY AVGRating ASC;

Upvotes: 0

GolezTrol
GolezTrol

Reputation: 116100

You missed the join condition for the feedback table:

SELECT
  c.id,l.login,
  AVG(f.mark) AS AVGRating
FROM logininfo l
  JOIN person p
    ON l.id = p.info_id
  JOIN candidate c
    ON p.id = c.id
  JOIN feedback f
    ON c.id = f.candidat_id -- < -- this one
GROUP BY c.id
ORDER BY AVGRating ASC;

Btw, INNER JOIN and JOIN are the same.

Upvotes: 2

Related Questions