[SOLVED] Subquery Help

[arianhojat]

I am trying to get associated values in another table but am getting an error:

'ERROR 1109 (42S02): Unknown table 'r' in where clause'

 

I know it exists. but not sure why mysql cant refer to outer table reference.

SELECT
r.RequestID,lastRoutedTemp.empNum

FROM
helpdesk.request r

LEFT JOIN
(
SELECT empNum, RequestID FROM helpdesk.routing rr
WHERE rr.RequestID=r.RequestID
ORDER BY rr.DateTimeEntered DESC
LIMIT 1
) as lastRoutedTemp ON r.RequestID=lastRoutedTemp.RequestID

/*
//once get above working, i will pull all reqeusts for user 80, which are open, and routed to IT support user 314
WHERE
Reqempnum=80
AND (r.Closed!=1)
-- AND lastRoutedID=314
*/

ORDER BY closed ASC, dateTimeEntered DESC

 

using server version: 4.1.12a-nt on server

[arianhojat]

whoops i think i got it another way of doing it.

 

build whole table this way, i dont think i can dynamically bring in the RequestID like be4.

 

i do get one perplexing error that i mention in below query, can i can get rid of but i want to use an alias:

 

SELECT
r.RequestID
,lastRoutedTemp.empnum as lastRouterID//this alias actually doesnt work. not sure why, need to get rid of it and use empnum or ill get "unknown column lastRouterID in where clause."

FROM
helpdesk.request r

LEFT JOIN
(
SELECT rr.RequestID, rr.empnum, MAX(rr.DateTimeEntered)
FROM routing rr
GROUP BY rr.RequestID
ORDER BY rr.RequestID ASC, rr.DateTimeEntered DESC
) as lastRoutedTemp ON r.RequestID=lastRoutedTemp.RequestID


WHERE
Reqempnum=80
AND (r.Closed!=1)
AND lastRouterID=314




ORDER BY closed ASC, dateTimeEntered DESC

[arianhojat]

nvm figured last one out too:

 

"Standard SQL doesn't allow you to refer to a column alias in a WHERE clause"

[RichardRotterdam]

I have the same problem here. Too bad the answer wasn't posted