Passing a value to a nested query in MySQL
I have the following MySQL query
SELECT lp.profileTitle, lp.lessonLength AS length, la.validFrom, la.validTo, ow.firstname, ow.surname, ow.outworker_id
FROM lessonProfile AS lp, lessonAvail AS la, outworker AS ow, outworkerAssign AS oa
WHERE lp.lessonProfileId = profileId
AND oa.lessonProfileId = lp.lessonProfileId
AND oa.outworkerId = ow.outworker_id
AND validFrom <= '2011-08-23'
AND (validTo >= '2011-08-23' OR validTo = '0000-00-00')
AND ow.outworker_id <> (SELECT ow.outworker_id
FROM outworker AS ow, bookingDiary AS bd
WHERE bd.outworkerId = ow.outworker_id
AND (bd.startTime BETWEEN '2011-08-23 12:00:00' AND DATE_ADD('2011-08-23 12:00:00', INTERVAL 60 MINUTE)
OR DATE_ADD(bd.endTime, INTERVAL (IFNULL(break,0)) MINUTE) BETWEEN '2011-08-23 12:00:00' AND DATE_ADD('2011-08-23 12:00:00', INTERVAL 60 MINUTE)
OR bd.startTime <= '2011-08-23 12:00:00' AND DATE_ADD(bd开发者_运维知识库.endTime, INTERVAL (IFNULL(break,0)) MINUTE) >= DATE_ADD('2011-08-23 12:00:00', INTERVAL 60 MINUTE)))
I need to pass the length value from the first line to the nested query in place of the INTERVAL 60 MINUTE
So that the nested query would be INTERVAL length MINUTE
But I'm stuck!
You can reference it inside the nested query simply as lp.lessonLength
.
精彩评论