Why this error? :
#1054 - Unknown column 'm2.s_sender_id' in 'where clause'
in this query:in this query: I need to pass m2.s_sender_id in this place!
select w.ct,m5.pc, w.s_thread_id,w.unread , w.draft , w.s_user_id , m2.s_subject ,m2.s_sender_id starter, LEFT(m3.s_message,20) , m3.s_date_sent from
(SELECT max(m.id) mxid , min(m.id) mnid ,min(r.s_unread_count) unread ,
COUNT(IF(m.s_sender_id = 534 and m.s_status = 0,1,NULL)) draft,
COUNT(IF(m.s_sender_id = 534 or m.s_sender_id = 2,1,NULL)) pc,
count(r.s_thread_id) ct ,
r.s_user_id , r.s_thread_id
FROM t_messages_messages m
LEFT JOIN t_messages_recipients r on (m.s_thread_id = r.s_thread_id )
where r.s_is_deleted = 0 AND r.s_user_id = 534 AND r.s_sender_only = 0 AND (r.s_is_arshived is null or r.s_is_arshived = 0)
GROUP BY m.s_thread_id) w
inner join t_messages_messages m2 on (m2.id=w.mnid)
inner join t_messages_messages m3 on (m3.id=w.mxid)
inner join (
SELECT COUNT( m4.id ) pc FROM t_messages_messages m4
where ( m4.s_sender_id = 534 or m4.s_sender_id = m2.s_sender_id )
) m5 on (m5.s_thread_id = w.s_thread_id)enter code here
The problem is with your derived table:
(
SELECT COUNT( m4.id ) pc FROM t_messages_messages m4
where ( m4.s_sender_id = 534 or m4.s_sender_id = m2.s_sender_id )
) m5 on (m5.s_thread_id = w.s_thread_id) enter
It has to be able to run on it's own and it can't because m2 does not exist in this subquery. If you want to refer to m2, you have to do it either do it outside the derived table, or incorporate m2 into your derived table.