mysql inner join-如何使用join子句中第一个表列的值

在我的查询中,我试图返回每个餐厅还有多少空座位和时段(活动时间编号)

这是 fiddle

桌子:

CREATE TABLE `restaurants` (
  `id` int(10) UNSIGNED NOT NULL,
  `title` text COLLATE utf8mb4_unicode_ci,
  `number_of_seats_max` int(11) DEFAULT NULL
);

CREATE TABLE `bookings` (
  `id` int(10) UNSIGNED NOT NULL,
  `event_date` timestamp NULL DEFAULT NULL,
  `event_time` int(11) NOT NULL,
  `number_of_persons` int(11) NOT NULL,
  `restaurant_id` int(11) NOT NULL
);

r.number_of_seats_max 座位数量最大值 unknown column “。

SELECT r.title, r.number_of_seats_max, innerquery.free_seats_left,
innerquery.num_persons_booked
FROM restaurants r
INNER JOIN(
    select 
        restaurant_id,
        SUM(number_of_persons) as num_persons_booked,
        (80 - SUM(number_of_persons)) AS free_seats_left   // <-- 80 is hard coded
        from bookings
        WHERE event_date = '2019-07-18'
        group by event_time,restaurant_id
        ORDER BY free_seats_left DESC

) as innerquery
     ON innerquery.restaurant_id = r.id;