portlight
asked on
need to add a 3rd join in from statement - giving error
I am trying to add a 3rd left join however once I added that second select statement I am getting errors.
There error is
1 Unknown column 'TimeOrder.completed' in 'on clause'
So I am thinking I do not have them nested correctly.
The full code I am using is
(SELECT T2.completed,
(SELECT MAX(completed)
FROM log',mylogID, '
WHERE completed < T2.completed) AS 1stcompleted
FROM log',mylogID, ' T2
ORDER BY T2.completed
)TimeOrder2
There error is
1 Unknown column 'TimeOrder.completed' in 'on clause'
So I am thinking I do not have them nested correctly.
The full code I am using is
SELECT
@row := @row + 1 AS rownum,
a.logId,
L1.recordId,
L2.recordId AS next_recordId,L3.completed AS 1stCompleted,
L1.completed,
L2.completed AS next_completed,
L1.activityId AS activityJoin,
L2.activityId AS next_activityId,
IF(L1.activityId = L2.activityId, 1, NULL) AS isError,
CASE
when a.name LIKE ''%Enable%'' THEN time_to_sec(timediff(L2.completed, L1.completed)) / 3600
ELSE NULL
END AS coolingHours,
TO_SECONDS(L2.completed) - TO_SECONDS(L1.completed) AS timeInSeconds,
((SUBSTR(l.details, INSTR(l.details, '':'') + 1))) AS charge,
time_to_sec(timediff(''', endDate, ''', ''', startDate, ''')) / 3600 AS totalTimeRange,
CONVERT_TZ(''', startDate, ''', ''UTC'', ''US/Pacific'') AS startingDate,
CONVERT_TZ(''', endDate, ''', ''UTC'', ''US/Pacific'') AS endingDate,
((L1.item', myItemID, ' - L1.item', myItemID2, ') *
(TIME_TO_SEC(TIMEDIFF(L2.completed, L1.completed)))) / 3600 AS kwDifference,
((L1.item', myItemID, ' - L1.item', myItemID2, ') * (SUBSTR(l.details, INSTR(l.details, '':'') + 1))) AS cost,
((((L1.item', myItemID, ' - L1.item', myItemID2, ') * (SUBSTR(l.details, INSTR(l.details,'':'') + 1)))
* (TIME_TO_SEC(TIMEDIFF(L2.completed, L1.completed)) / 3600))) AS costT,
DATABASE() AS databaseName, i.itemId ,l.details
FROM
(SELECT @row:=0)R,
(SELECT T1.completed,
(SELECT MIN(completed)
FROM log',mylogID, '
WHERE completed > T1.completed) AS next_completed
FROM log',mylogID, ' T1
ORDER BY T1.completed
)TimeOrder,(SELECT T2.completed,
(SELECT MAX(completed)
FROM log',mylogID, '
WHERE completed < T2.completed) AS 1stcompleted
FROM log',mylogID, ' T2
ORDER BY T2.completed
)TimeOrder2
LEFT JOIN log', mylogID, ' L1 ON (L1.completed = TimeOrder.completed)
LEFT JOIN log', mylogID, ' L2 ON (L2.completed = TimeOrder.next_completed)
LEFT JOIN log', mylogID, ' L3 ON (L3.completed = TimeOrder2.1stcompleted)
LEFT JOIN activities a ON L1.activityId = a.activityId
LEFT JOIN logs l ON a.logId = l.logId
inner join items i ON l.logId = i.logId AND i.name LIKE ''%KW PRE%''
)ORDER BY L1.completed')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Try changing the alias name. Generally, columns and aliases begin with letters. It's also listed as a general best practice. You might also want to fully qualify the 'completed' column in the WHERE clause.
Best regards,
AielloJ