subquery is not working in mysql

subquery is not working in mysql

This is my mysql query
SELECT tm.MAGAZINE_ID,
tm.MAGAZINE_NAME,tm.MAGAZINE_DESCRIPTION,pub.publisher_name,
tmi.COVER_PAGE_THUMB AS COVER_PAGE_VERTICAL,tmi.FROM_DATE
AS ISSUE_DATE,
tm.html_flag AS
HTML_EXIST,tm.CATEGORY_ID,tm.language_id,tm.is_free,tma.AppUrl,
(SELECT issue_id from tbl_magazine_issue WHERE magazine_id
= 141
ORDER BY FROM_DATE DESC LIMIT 1) as temp_issue_id
FROM tbl_magazine_apps as tma
LEFT OUTER JOIN tbl_magazine_code as tmc ON tmc.Code =
tma.AppsCode
LEFT OUTER JOIN `tbl_magazine` AS tm ON tmc.magazine_Id =
tm.MAGAZINE_ID
JOIN `tbl_magazine_issue` AS tmi ON temp_issue_id =
tmi.issue_id
LEFT OUTER JOIN mst_publisher AS pub ON
tm.publisher_id=pub.publisher_id
WHERE
tmi.PUBLISH_STATUS IN(1,3)
AND tmi.`OS_SELECT` = '".$osType."'
AND tma.id IN (".$appIds.")
GROUP BY tm.MAGAZINE_ID
ORDER BY tmi.ISSUE_DATE DESC
but i got an error that
#1054 - Unknown column 'temp_issue_id' in 'on clause'
if any one know about this please help me. i am new to this