0

I'm getting the following error while running this query: Error #1248 - Every derived table must have its own alias 

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 your_duration HOURS
)
AND a.end BETWEEN start_of_search_window AND end_of_search_window
)
ORDER BY free_until-free_from
LIMIT 0,3;
Abhik Chakraborty
  • 44,654
  • 6
  • 52
  • 63
Nitin Tolani
  • 31
  • 3

1 Answers1

0

Every subquery in the from clause ("derived table" as MySQL calls them) needs to have a name. This is a table alias.

If you format your queries so they are more readable, you can probably readily see where the alias is needed:

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 your_duration HOURS
                       ) AND
            a.end BETWEEN start_of_search_window AND end_of_search_window
     ) t
-------^
ORDER BY free_until-free_from
LIMIT 0,3;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • Thanks, it worked. I'm also facing another problem with this query though. When the result is displayed- in the very last record, it only shows the 'free_from' value and the 'free_until' value is NULL. Please help me solve this. – Nitin Tolani Apr 27 '14 at 13:04
  • @NitinTolani . . . You should ask another question, with sample data. – Gordon Linoff Apr 27 '14 at 14:06