CODEWITHSUNDEEP
mysql
Niladri Banerjee - Uttarpara
Niladri Banerjee - Uttarpara

Reputation: 2329

mysql - Every derived table must have its own alias

Getting error "#1248 - Every derived table must have its own alias" when running the following query through phpMyAdmin:

SELECT 
    `free_from`, `free_until`
FROM 
(
    SELECT 
        a.end AS `free_from`,
        (SELECT MIN(c.start) FROM bookings c WHERE c.start>a.end) as `free_until`
    FROM bookings a
    WHERE NOT EXISTS (
        SELECT 1
        FROM bookings b
        WHERE b.start BETWEEN a.end AND a.end + INTERVAL 30 Minute
    ) 
    AND a.end BETWEEN '2012-11-16 09:00:00' AND '2012-11-16 22:00:00' 
)

Can you please help me in this regard?

Upvotes: 0

Views: 61

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269873

The immediate problem is that your outer query needs an alias. But, you don't need a subquery at all for this logic. And, because MySQL tends to materialize subqueries, this improves performance:

SELECT a.end AS `free_from`,
       (SELECT MIN(c.start)
        FROM bookings c
        WHERE c.start > a.end
       ) as `free_until`    
FROM bookings a
WHERE NOT EXISTS (SELECT 1
                  FROM bookings b
                  WHERE b.start BETWEEN a.end AND a.end + INTERVAL 30 Minute
                 ) AND
      a.end BETWEEN '2012-11-16 09:00:00' AND '2012-11-16 22:00:00';

Upvotes: 1

Related Questions