cPanel Question posted in
The official documentation can be seen here.

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

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?

Tags:

2

Answers


  1. 0

    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]
;
    Login or Signup to reply.
  2. 0

    Seems like there’s a couple of ON clauses missing.

    I know that RIGHT outer joins are supported, but why would we write it that way, and not write it as LEFT outer joins. (We typically reserve RIGHT joins to the towers of academia.)

    And it’s well past time to ditch the old-school comma syntax for join operations. (Yes, it’s still supported for backwards compatibility with existing statements. But new development should use the newer JOIN syntax.)

    The condition requiring a non-NULL value of F.following would effectively negate the “outerness” of the join, rendering it equivalent to an INNER join. For clarity, we should either write that as an inner JOIN, or if we want an outer join, we should relocate that condition to the appropriate ON clause.

    Also, best practice is to qualify all column references; even when they aren’t ambiguous to the optimizer, it makes it easier on the future reader (so the future reader doesn’t have to confirm which table contains the id column), as well as protecting the query from throwing “ambiguous column” errors in the future if a column named id is added to another table used by the query.

    Also, it’s not valid to reference columns from F in the outer query inside inline view queries. We can use a correlated subquery, but not as an inline view.

    The specification isn’t clear. Example data and sample of expected output would go a long ways to clarifying the requirements.

    If we want to use correlated subqueries that return a single row, with a single column, we can put those in the SELECT list …

    SELECT f.*
     , u.*
     , ( SELECT COUNT(m.id)
           FROM experiences m
          WHERE m.userid = f.userid
       ) AS jobs
     , ( SELECT SUM(s.stars)
           FROM experiences s
          WHERE s.userid = f.userid
       ) AS stars
  FROM followers f
  LEFT
  JOIN users u 
    ON u.userid = f.userid
 WHERE f.following = 1     /* #1 = the user # I want the follwers of/for */
 ORDER BY ...

    We could get an equivalent result using inline views, but that would look quite different.

    I would tend to do the aggregation inside the inline view, something along the lines of this:

    SELECT f.*
     , u.*
     , IFNULL(e.jobs,0) AS jobs
     , IFNULL(e.stars,0) AS stars
  FROM followers f
  LEFT
  JOIN users u
    ON u.userid = f.userid
  LEFT  
  JOIN ( SELECT ef.userid 
              , COUNT(ee.id)   AS jobs
              , SUM(ee.stars)  AS stars
           FROM followers ef
           JOIN experiences ee
             ON ee.userid = ef.userid
          WHERE ef.following = 1       /* argument */
          GROUP BY ef.userid
       ) e
   ON e.userid = f.userid
WHERE f.following = 1                  /* argument */   
ORDER BY ...
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search