MySQL - Referencing outer table value from double nested subquery

Question

The query I really need to execute is follows:

SELECT      u.points
        (SELECT COUNT(1) FROM (SELECT 1 FROM checkin c INNER JOIN wineries w ON w.id = c.winery_id WHERE c.user_id = u.id GROUP BY region_id) b) as states_visited
FROM        users u
GROUP BY u.id
ORDER BY points DESC

However, this causes the following error:

Unknown column 'u.id' in 'where clause'

I've tried with user-defined variables, no errors, but it's not actually referencing the user-defined variable value for some reason:

SELECT      @uid := u.id, u.points
        (SELECT COUNT(1) FROM (SELECT 1 FROM checkin c INNER JOIN wineries w ON w.id = c.winery_id WHERE c.user_id = @uid GROUP BY region_id) b) as states_visited
FROM        users u
GROUP BY u.id
ORDER BY points DESC

Any thoughts how I can make this work? Without the obvious resorting to doing two separate queries?

Can you simply explain the tables in use, their schema, and what you are actually trying to get in your query result? — Mike Brant
– Mike Brant, Commented Nov 26, 2013 at 19:54
can you set up some test data on sqlFiddle.com and explain what results you're expecting — Tin Tran
– Tin Tran, Commented Nov 26, 2013 at 20:56

ypercubeᵀᴹ · Accepted Answer · 2013-11-26 19:59:44Z

2

Why do you need the double nesting? And if the id is the primary key of table user, you don't need the GROUP BY either:

SELECT      u.points,
        (SELECT COUNT(1) FROM reviews WHERE user_id = u.id) AS review_count
FROM        users AS u
-- GROUP BY u.id
ORDER BY points DESC ;

You could also GROUP BY in a derived table - and then join:

SELECT      u.points,
            COALESCE(r.review_count,0) AS review_count
FROM        users AS u
    LEFT JOIN
        (SELECT user_id, COUNT(1) AS review_count
         FROM reviews
         GROUP BY user_id
        ) AS r
        ON  r.user_id = u.id
ORDER BY points DESC ;

or join and then GROUP BY:

SELECT      u.points,
            COUNT(r.user_id) AS review_count
FROM        users AS u
    LEFT JOIN
            reviews AS r
        ON  r.user_id = u.id
GROUP BY u.id, u.points
ORDER BY points DESC ;

The edited version is harder but can be done without double nesting, too:

SELECT      u.points,
        (SELECT COUNT(DISTINCT region_id) 
         FROM checkin c INNER JOIN wineries w ON w.id = c.winery_id 
         WHERE c.user_id = u.id 
        ) AS states_visited
FROM        users u
ORDER BY points DESC ;

edited Nov 26, 2013 at 19:59

answered Nov 26, 2013 at 19:41

ypercubeᵀᴹ

116k19 gold badges181 silver badges248 bronze badges

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

3 Comments

Phil LaNasa Over a year ago

Apologies, I tried to simplify my query before posting, but I took the hard part out of it! Please see the revised subquery, which itself has a join that creates multiple rows.

ypercubeᵀᴹ Over a year ago

OK, it is harder, indeed. Remove the double nesting and the internal GROUP BY and use COUNT(DISTINCT region_id)

Phil LaNasa Over a year ago

You sir, are a genious! I love solutions that actually reduce the complexity of the query. Thanks again.

Mike Brant · Accepted Answer · 2013-11-26 19:44:08Z

0

It seems like you can do what you want with a simpe table join

SELECT u.id AS `id`, u.points AS `points`, COUNT(r.review_id) AS `review_count` /* or whatever the id record for reviews table is */
FROM users AS u
INNER JOIN reviews AS r
  ON u.id = r.user_id
GROUP BY `id`
ORDER BY `points` DESC

answered Nov 26, 2013 at 19:44

Mike Brant

71.4k10 gold badges102 silver badges104 bronze badges

2 Comments

Phil LaNasa Over a year ago

Thanks for the reply, sorry, I had to make an edit, see comment in ypercube's reply.

Mike Brant Over a year ago

@PhilL I still don;t understand why you would need a subselect at all. Maybe it would help if, in your question, you describe your tables and what exactly you are trying to get in your query.

Tin Tran · Accepted Answer · 2013-11-26 19:53:59Z

0

SELECT users.id, users.points, count(*)
FROM users, reviews
WHERE users.id = reviews.user_id
GROUP BY id,points
ORDER BY points DESC

sqlFiddle example

answered Nov 26, 2013 at 19:53

Tin Tran

6,1763 gold badges25 silver badges34 bronze badges

Collectives™ on Stack Overflow

MySQL - Referencing outer table value from double nested subquery

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related