Внутреннее соединение MySQL в поле предложения не найдено

Question

Мои текущие таблицы выглядят так:

Lessons:
+-----------------------------------+
| ID | Name  | StartDate  | Repeats |
|----|-------|------------|---------|
| 1  | Maths | 2014-05-05 |    5    |
| 2  | Lunch | 2014-05-05 |    1    |
| 3  | Comp  | 2014-05-05 |    7    |
+-----------------------------------+

LessonTimes:
+-------------------------------------+
| ID | LessonID | StartTime | EndTime |
|----|----------|-----------|---------|
| 1  |    1     | 10:00:00  |    5    |
| 2  |    2     | 12:25:00  |    1    |
| 3  |    3     | 14:00:00  |    7    |
+-------------------------------------+

Tally:
+----+
| ID |
|----|
| 1  |
| 2  |
| .  |
| .  |
+----+

У меня есть события, которые повторяются в определенное количество дней с определенной датой начала. Текущий запрос у меня есть:

SELECT E.ID
     , E.Name
     , E.StartDate
     , E.Repeats
     , A.ShowDate
     , DATEDIFF(E.StartDate, A.ShowDate) diff
     , T.StartTime
     , DATE_ADD(A.ShowDate, INTERVAL T.StartTime HOUR_SECOND) ShowTime 
  FROM Planner_Lessons E
     , ( SELECT DATE_ADD('2014-05-05 00:00:00',INTERVAL ID DAY ) ShowDate
           FROM `Planner_Tally`
          WHERE (DATE_ADD('2014-05-05 00:00:00',INTERVAL ID DAY )<= '2014-05-30 00:00:00')
          ORDER 
             BY Id ASC 
       ) A 
    LEFT 
    JOIN Planner_LessonTimes T 
      ON T.LessonID = E.ID 
   WHERE MOD(DATEDIFF(E.StartDate, A.ShowDate), E.Repeats) = 0 
     AND A.ShowDate >= E.StartDate

Но ошибка, которую я получаю, говорит, что поле E.ID не может быть найден в предложении "ON".

Оригинальный вопрос, по которому я нашел запрос, находится здесь - PHP / MySQL: модель повторения событий в базе данных, но запрос диапазонов дат

Answer 1

Вы пропустили условие JOIN для подзапроса с псевдонимом A. Если предположить, Planner_Tally таблица содержит столбец ID, вы можете добавить условие соединения ON A.ID=E.ID как показано ниже

SELECT E.ID, E.Name, E.StartDate, E.Repeats, A.ShowDate, 
       DATEDIFF(E.StartDate, A.ShowDate) AS diff, T.StartTime,
       DATE_ADD(A.ShowDate, INTERVAL T.StartTime HOUR_SECOND) AS ShowTime 
FROM Planner_Lessons AS E
LEFT JOIN Planner_LessonTimes AS T ON T.LessonID=E.ID 
LEFT JOIN (
           SELECT DATE_ADD('2014-05-05 00:00:00',INTERVAL ID DAY) as ShowDate,ID
           FROM `Planner_Tally`
           WHERE (DATE_ADD('2014-05-05 00:00:00',INTERVAL ID DAY)<='2014-05-30 00:00:00')
 ) A  ON A.ID=E.ID 
WHERE MOD(DATEDIFF(E.StartDate, A.ShowDate), E.Repeats)=0 
      AND A.ShowDate>=E.StartDate
ORDER BY E.Id ASC

Answer 2

Вот ваш запрос, форматирование, чтобы его можно было прочитать:

SELECT E.ID, E.Name, E.StartDate, E.Repeats, A.ShowDate, DATEDIFF(E.StartDate, A.ShowDate) AS diff,
       T.StartTime, DATE_ADD(A.ShowDate, INTERVAL T.StartTime HOUR_SECOND) AS ShowTime
FROM Planner_Lessons AS E,
     (SELECT DATE_ADD('2014-05-05 00:00:00',INTERVAL ID DAY) as ShowDate
      FROM `Planner_Tally`
      WHERE (DATE_ADD('2014-05-05 00:00:00',INTERVAL ID DAY)<='2014-05-30 00:00:00')
      ORDER BY Id ASC
     ) A LEFT JOIN
     Planner_LessonTimes AS T
     ON T.LessonID=E.ID
WHERE MOD(DATEDIFF(E.StartDate, A.ShowDate), E.Repeats)=0 AND A.ShowDate>=E.StartDate;

Вам не хватает явного и явного join синтаксис. Столбцы в E не распознаются после запятой, из-за правил определения области действия MySQL.

SELECT E.ID, E.Name, E.StartDate, E.Repeats, A.ShowDate, DATEDIFF(E.StartDate, A.ShowDate) AS diff,
       T.StartTime, DATE_ADD(A.ShowDate, INTERVAL T.StartTime HOUR_SECOND) AS ShowTime
FROM Planner_Lessons E JOIN
     Planner_LessonTimes T
     ON T.LessonID = E.ID JOIN
     (SELECT DATE_ADD('2014-05-05 00:00:00',INTERVAL ID DAY) as ShowDate
      FROM `Planner_Tally`
      WHERE (DATE_ADD('2014-05-05 00:00:00',INTERVAL ID DAY)<='2014-05-30 00:00:00')
      ORDER BY Id ASC
     ) A 
     ON MOD(DATEDIFF(E.StartDate, A.ShowDate), E.Repeats)=0 AND A.ShowDate>=E.StartDate;

Я переключил left join к внутреннему соединению, потому что where пункт отменяет внешнее соединение.

Answer 3

В качестве отступления рассмотрим следующее... (ints таблица целых чисел от 0 до 9)

EXPLAIN
SELECT * FROM ints WHERE '2014-05-05 00:00:00' + INTERVAL i DAY < '2014-30-30 00:00:00'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ints
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10
        Extra: Using where; Using index
1 row in set (0.00 sec)

EXPLAIN
SELECT * FROM ints WHERE i < DATEDIFF('2014-05-30 00:00:00','2014-05-05 00:00:00')\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: ints
         type: index
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 10
        Extra: Using where; Using index
1 row in set (0.00 sec)

Как видите, хотя оба запроса логически идентичны, первый регистрирует NULL для possible_keys,

Теперь мы достигли полной степени моего знания индексов.