Hi,

I have query like,

Code:
SELECT u.user_id, u.user_first_name, CASE WHEN user_login_date = '0000-00-00 00:00:00' AND user_login_date = 'NULL' THEN (SELECT u.user_login_date AS user_last_login_date ) ELSE (SELECT MAX( login_date ) AS user_last_login_date FROM login_history WHERE login_user_id = u.user_id AND login_program_id =298 ) END 'user_last_login' FROM program_subscription AS p LEFT JOIN user AS u ON u.user_id = p.program_subscription_user_id WHERE p.program_subscription_program_id =298 AND u.user_company_id =1 UNION SELECT u.user_id, u.user_first_name, CASE WHEN user_login_date = '0000-00-00 00:00:00' AND user_login_date = 'NULL' THEN (SELECT u.user_login_date AS user_last_login_date ) ELSE (SELECT MAX( login_date ) AS user_last_login_date FROM login_history WHERE login_user_id = u.user_id AND login_program_id =298 ) END 'user_last_login' FROM program_subscription AS p LEFT JOIN user AS u ON u.user_id = p.program_subscription_user_id LEFT JOIN user_other_company as lu ON u.user_id=lu.user_id WHERE p.program_subscription_program_id =298 AND ( u.user_company_id in (74,97,136,106,108,119,75,76,77,155) OR lu.user_other_company_id in (74,97,136,106,108,119,75,76,77,155)) ORDER BY CASE WHEN user_login_date != '0000-00-00 00:00:00' AND user_login_date = 'NULL' THEN (SELECT u.user_login_date AS user_last_login_date ) ELSE (SELECT MAX( login_date ) AS user_last_login_date FROM login_history WHERE login_user_id = u.user_id AND login_program_id =298 ) END ASC LIMIT 0, 5
This query was generating an error -Error Number: 1054.Unknown column 'user_login_date' in 'order clause'. So I modified the query as,

Code:
SELECT u.user_id, u.user_first_name, CASE WHEN user_login_date = '0000-00-00 00:00:00' AND user_login_date = 'NULL' THEN (SELECT u.user_login_date AS user_last_login_date ) ELSE (SELECT MAX( login_date ) AS user_last_login_date FROM login_history WHERE login_user_id = u.user_id AND login_program_id =298 ) END 'user_last_login' FROM program_subscription AS p LEFT JOIN user AS u ON u.user_id = p.program_subscription_user_id WHERE p.program_subscription_program_id =298 AND u.user_company_id =1 UNION SELECT u.user_id, u.user_first_name, CASE WHEN user_login_date = '0000-00-00 00:00:00' AND user_login_date = 'NULL' THEN (SELECT u.user_login_date AS user_last_login_date ) ELSE (SELECT MAX( login_date ) AS user_last_login_date FROM login_history WHERE login_user_id = u.user_id AND login_program_id =298 ) END 'user_last_login' FROM program_subscription AS p LEFT JOIN user AS u ON u.user_id = p.program_subscription_user_id LEFT JOIN user_other_company as lu ON u.user_id=lu.user_id WHERE p.program_subscription_program_id =298 AND ( u.user_company_id in (74,97,136,106,108,119,75,76,77,155) OR lu.user_other_company_id in (74,97,136,106,108,119,75,76,77,155)) ORDER BY CASE WHEN user_last_login != '0000-00-00 00:00:00' AND user_last_login = 'NULL' THEN (SELECT u.user_login_date AS user_last_login_date ) ELSE (SELECT MAX( login_date ) AS user_last_login_date FROM login_history WHERE login_user_id = u.user_id AND login_program_id =298 ) END ASC LIMIT 0, 5
Now I am getting an error - #2006 -> MySQL server has gone away .What might be the reason for this error. If the above modified query has no results, then a message "no rows found" will be shown up.If any result is returned then mysql time out error is being shown.Can anyone help me to figure out the issue.
When I referred certain tutorials they were telling to chsnge the settings of mysql so as to overcome the timing out problem. Is there any other way to overcome the issue?