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

Question

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?

Any particular reason you are using `RIGHT JOIN`? There is nothing inherently wrong with; I just seldom see it used in well composed queries. — Uueerdo, Apr 10 '18 at 21:03
No real reason. I edited my question and showed the other query I've tried. Same result. Except the F.userid is in the SELECT with the COUNT() — ppetree, Apr 10 '18 at 21:05
It's also not a good idea to mix "comma joins" and explicit joins; comma joins are generally considered archaic (at best) as they have been out of favor for decades. — Uueerdo, Apr 10 '18 at 21:06

Uueerdo · Accepted Answer · 2018-04-10T21:21:15.517

1

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]
;

edited Apr 10 '18 at 21:21

answered Apr 10 '18 at 21:10

Uueerdo

15,723
1
16
21

Sorry for the slow response... storms killed Internet... just back online. I tried your first query and that took me back to where I started: Syntax error at WHERE F.following = 1; I'll have to unpack the 2nd one and make sure I fully understand it before I try it. – ppetree Apr 11 '18 at 18:37
Oh, I see looks like Spencer's answer caught the problem with first one (missing on clauses).... it looks like outer joins require them; if you still want to go with the first version (closest to your own), you can just add `ON 1= 1` to each join; but you're probably just better off going with my second suggestion. _If you need to do hacky stuff like `ON 1=1`, it is usually "bad form"._ – Uueerdo Apr 11 '18 at 19:19
I was just bench marking your and Spencer's answers. Your rework comes in faster. 0.0012 seconds to his 0.0017 seconds. I feel old. ;-) – ppetree Apr 11 '18 at 20:05

spencer7593 · Answer 2 · 2018-04-10T21:30:33.787

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

Thanks for the great answer Spencer. In unpacking yours I learned a great deal. I gave the win to Uueerdo only because his query executed faster and was actually easier for me to understand (although that was def secondary). His query executed at 0.0012 seconds to your 0.0017 seconds. — ppetree, Apr 11 '18 at 20:09

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

2 Answers2