Search code examples
mysqlcountright-join

SELECT, 2 counts from 2nd table, RIGHT JOIN on 3rd

I'm trying to gather "followers" for a specific user (#1 in this code).

I'm doing my primary select from followers as the column following will have user #1 and followers.userid will have the userid of the person doing the following.

Next I'm trying to get a count of records from the experiences that have the user id of the follower (how many experiences does this follower have?)

Next, the follower will have rated each experience (1-5 stars) and I want to sum those ratings (experiences.stars) to get an average rating of all experiences.

Lastly, I want to join the followers user record from the users table.

I should end up with userid, jobs, stars, * from users

SELECT * FROM followers AS F
RIGHT JOIN 
  (SELECT count(id) FROM experiences AS M WHERE M.userid = F.userid) AS jobs
RIGHT JOIN
  (SELECT sum(stars) FROM experiences AS S WHERE S.userid = F.userid) AS stars
RIGHT JOIN 
  users AS U ON U.userid = F.userid
WHERE F.following = 1 /* #1 = the user # I want the follwers of/for */

I've also tried:

SELECT * FROM followers AS F,
  (SELECT count(id) FROM experiences AS M WHERE M.userid = F.userid) AS jobs,
  (SELECT sum(stars) FROM experiences AS S WHERE S.userid = F.userid) AS stars
RIGHT JOIN 
  users AS U ON U.userid = F.userid
WHERE F.following = 1 /* #1 = the user # I want the follwers of/for */

In cPanel, I'm getting an error that I have syntax error at WHERE F.userid in both statements.

A) what am I missing and B) is there a better way to do this?

Solution

  • It seems to me, the query would be easier to follow like so:

    SELECT * 
FROM followers AS F
LEFT JOIN users AS U ON U.userid = F.userid
LEFT JOIN (SELECT count(id) FROM experiences AS M WHERE M.userid = **F.userid)** AS jobs
LEFT JOIN (SELECT sum(stars) FROM experiences AS S WHERE S.userid = F.userid) AS stars
WHERE F.following = 1 /* #1 = the user # I want the follwers of/for */
;

    All those RIGHT JOINs you originally had would only give you followers that had both "types" of experiences.

    Also, correlated subqueries can be expensive (and you didn't need two of them...actually, you didn't even need subqueries), so I'd also rework it like so....

    SELECT F.*, U.*, count(x.id), sum(x.stars)
FROM followers AS F
LEFT JOIN users AS U ON U.userid = F.userid
LEFT JOIN experiences AS x ON F.userid = x.user_id
WHERE F.following = 1
GROUP BY [all the fields selected in F and U, or just F.userid if server settings allow]
;