MySQL Inner Join On Clause Field Not Found

Question

My current tables look like this:

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  |
| .  |
| .  |
+----+

I have events that repeat on a certain number of days with a specific start date. The current query I have is:

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

But the error I get is saying that the field E.ID cannot be found in the "ON" clause.

The original question I found the query on is here - PHP/MySQL: Model repeating events in a database but query for date ranges

i think you left join with `A` table so you can not set condition `T.LessonID=E.ID` — Sathish, May 06 '14 at 12:38
Planner_lessons and A have no on clause or inner/outer join syntax — xQbert, May 06 '14 at 12:43
Do not mix implicit (comma-) and explicit join syntax. In fact, don't use implicit join syntax at all. Also, I suspect that it's slightly more efficient to compare the 'id' with the result of DATEDIFF('2014-05-30 00:00:00','2014-05-05 00:00:00') — Strawberry, May 06 '14 at 12:49

score 0 · Answer 1 · answered May 06 '14 at 12:42

Here is your query, formatting so one can read it:

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;

You are missing implicit and explicit join syntax. The columns in E are not recognized after the comma, due to MySQL scoping rules.

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;

I switched the left join to inner join, because the where clause undoes the outer join.

score 0 · Answer 2 · answered May 06 '14 at 12:43

You have missed JOIN condition for subquery aliased as A . Assuming Planner_Tally table contains column ID , you can add joining condition ON A.ID=E.ID as shown below

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

score 0 · Answer 3 · answered May 06 '14 at 12:56

As an aside, consider the following... (ints is a table of integers from 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)

As you can see, although both queries are logically identical, the first registers NULL for possible_keys.

We have now reached the total extent of my knowledge of indices.

MySQL Inner Join On Clause Field Not Found

3 Answers3