Orig sql query, to find the current status of a payment. There is a payment_statuses table, if a payment has 4 statuses, 3 of them will have updated=true and the most recent will have updated=false
select A.id,A.order_id,A.payment_type,A.amount,P.status,P.date as input_date,
U.first_name,U.last_name,U.display_name
from payments as A
left join payment_statuses as P on(A.id=P.payment_id)
left join users as U on (P.updater_id=U.id)
where P.updated='f' and P.status !='7'
order by A.order_id
if i want to select latest status AND when it was entered, i used this one. but its giving duplicate info. query above returns 10 rows, one below returns 15
select *
from payments as A
left join payment_statuses as P on(A.id=P.payment_id)
left join payment_statuses as X on(A.id=X.payment_id)
where
P.status ='1' and X.status!='7' and X.updated='f'
order by A.order_id