Table alias unknown in nested subquery
The following query works just fine. I am using the value from the outer selection to filter inside the inner selection.
SELECT
bk.ID,
(SELECT COUNT(*) FROM guests WHERE BookingID = bk.ID) as count
FROM
bookings bk;
However, the following choice will not work:
SELECT
bk.ID,
(SELECT SUM(count) FROM (SELECT COUNT(*) AS count FROM guests WHERE BookingID = bk.ID GROUP BY RoomID) sub) as sumcount
FROM
bookings bk;
Error message: Error Code: 1054. Unknown column 'bk.ID' in 'where clause'
Why is my alias bk
known in the subquery but not in the subheading subquery?
For the record, I am using MySQL 5.6.
source to share
Correlated scalar subqueries in a SELECT list can usually be rewritten as a LEFT JOIN in a view (and may perform better in many cases):
SELECT
bk.ID,
dt.sumcount
FROM
bookings bk
LEFT JOIN
(SELECT BookingID,SUM(COUNT) AS sumcount
FROM
(
SELECT BookingID, RoomId, COUNT(*) AS COUNT
FROM guests
GROUP BY BookingID, RoomID
) sub
) AS dt
ON bk.BookingID = dt.BookingID
source to share
This is called scoping. I know that Oracle (for example) only looks one level down for resolving table aliases. SQL Server is also compatible: it looks at more than one level.
Based on this example, MySQL explicitly limits the scope of an identifier to a bk
direct subquery. There is a little hint in the documentation (emphasis mine):
A correlated subquery is a subquery that contains a link to a table that also appears in the outer query .
However, I have not found any other specific reference to scoping rules in the documentation. There are other answers ( here and here ) that indicate that the scope of a table alias is limited to one level of subquery.
You already know how to fix the problem (your two queries should give the same results). Reorganizing the query to combine and aggregate can also solve this problem.
source to share