SELECT count(*) as cnt FROM (
SELECT job_mail.*,
CASE WHEN nuser.mail IS NOT NULL THEN nuser.mail
WHEN cuser.mail IS NOT NULL THEN cuser.mail
WHEN admin.mail IS NOT NULL THEN admin.mail
ELSE consultant.mail END AS mail,
CASE WHEN nuser.nick_name IS NOT NULL THEN nuser.nick_name
WHEN cuser.nick_name IS NOT NULL THEN cuser.nick_name
WHEN admin.name IS NOT NULL THEN admin.name
ELSE consultant.nick_name END AS nick_name,
topic.subject,private_message.content,private_message.author_id,private_message_user.user_id
FROM job_mail
left join topic on job_mail.topic_id = topic.id
left join private_message on job_mail.item_id = private_message.id
left join private_message_user on private_message.id = private_message_user.pm_id
left join nuser on private_message_user.user_id = nuser.id
left join cuser on private_message_user.user_id = cuser.id
left join consultant on private_message_user.user_id = consultant.id
left join admin on private_message_user.user_id = admin.id
WHERE (private_message_user.is_draft = 0 and private_message_user.is_read = 0
and job_mail.template != 'ADD_USER' and job_mail.status = 'waiting' and job_mail.id = 'JM0000052')
) as result_table
SELECT job_mail.*,
CASE WHEN nuser.mail IS NOT NULL THEN nuser.mail
WHEN cuser.mail IS NOT NULL THEN cuser.mail
WHEN admin.mail IS NOT NULL THEN admin.mail
ELSE consultant.mail END AS mail,
CASE WHEN nuser.nick_name IS NOT NULL THEN nuser.nick_name
WHEN cuser.nick_name IS NOT NULL THEN cuser.nick_name
WHEN admin.name IS NOT NULL THEN admin.name
ELSE consultant.nick_name END AS nick_name,
topic.subject,private_message.content,private_message.author_id,private_message_user.user_id
FROM job_mail
left join topic on job_mail.topic_id = topic.id
left join private_message on job_mail.item_id = private_message.id
left join private_message_user on private_message.id = private_message_user.pm_id
left join nuser on private_message_user.user_id = nuser.id
left join cuser on private_message_user.user_id = cuser.id
left join consultant on private_message_user.user_id = consultant.id
left join admin on private_message_user.user_id = admin.id
WHERE (private_message_user.is_draft = 0 and private_message_user.is_read = 0
and job_mail.template != 'ADD_USER' and job_mail.status = 'waiting' and job_mail.id = 'JM0000052')
) as result_table
Comments
Post a Comment