CODEWITHSUNDEEP
mysqlsql
O-mkar
O-mkar

Reputation: 5658

How to access value from outer query in inner query in MySQL

SELECT * FROM events e LEFT JOIN venue v ON v.id=e.venueid 
CROSS JOIN (select (((5*sum(rating = 5))+(4*sum(rating = 4))+(3*sum(rating = 3))+(2*sum(rating = 2))+sum(rating = 1))/count(*)) rating 
from feedbacks where boxid = **e.boxid**) r 
WHERE e.startdate = CURDATE() AND e.starttime >= CURTIME() 
LIMIT 10;

I can't access e.boxid inside the subquery. Can anybody help?

Upvotes: 1

Views: 236

Answers (2)

Gurwinder Singh
Gurwinder Singh

Reputation: 39517

use a GROUP BY on boxid to bring it outside the subquery and then join it with events table on boxid column like this:

SELECT 
    *
FROM
    events e
        LEFT JOIN
    venue v ON v.id = e.venueid
        JOIN
    (SELECT 
        boxid,
            (((5 * SUM(rating = 5)) + (4 * SUM(rating = 4)) + (3 * SUM(rating = 3)) + (2 * SUM(rating = 2)) + SUM(rating = 1)) / COUNT(*)) rating
    FROM
        feedbacks
    GROUP BY boxid) r ON e.boxid = r.boxid
WHERE
    e.startdate = CURDATE()
        AND e.starttime >= CURTIME()
LIMIT 10;

You could also use correlated subquery like this:

SELECT e.*,
  v.*,
  (SELECT (((5*SUM(rating = 5))+(4*SUM(rating = 4))+(3*SUM(rating = 3))+(2*SUM(rating = 2))+SUM(rating = 1))/COUNT(*)) AS rating
  FROM feedbacks f
  WHERE f.boxid = e.boxid
  ) rating
FROM events e
LEFT JOIN venue v
ON v.id           = e.venueid
WHERE e.startdate = CURDATE()
AND e.starttime  >= CURTIME() LIMIT 10;

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1270713

Another approach to this uses a correlated subquery:

SELECT e.*, v.*,
       (SELECT (((5*sum(rating = 5))+(4*sum(rating = 4))+(3*sum(rating = 3))+(2*sum(rating = 2))+sum(rating = 1))/count(*)) as rating 
        FROM feedbacks f
        WHERE f.boxid = e.boxid
       ) as rating
FROM events e LEFT JOIN 
     venue v
     ON v.id = e.venueid 
WHERE e.startdate = CURDATE() AND e.starttime >= CURTIME() 
LIMIT 10;

In this situation, because the WHERE clause filters out many records (presumably), the correlated subquery could be much more efficient than the GROUP BY. This is especially true if you have an index in feedbacks(boxid).

Upvotes: 2

Related Questions