SELECT t_course_transaction.record_status, tm_course_payment.id as id_payment, t_course_transaction.id as id_transaction, tr_course_transaction_customer.id_course_member, `t_course_transaction`.`invoice`, `t_course_member`.*, `tm_course`.`title`, `t_course_transaction`.`record_create_timestamp`, `t_course_payment_wiretransfer`.`bank_name`, `t_course_payment_wiretransfer`.`account_number`, `t_course_transaction_coupon`.`total_amount` as `coupon_amount`, `t_course_transaction_coupon`.`code` as `coupon_code`, `tm_course_payment`.`amount`, `t_course_payment_status`.`id_course_payment`, `t_course_payment_status`.`status`, `tm_users_accounts`.`email`, `t_members_profiles`.`name_first`, `t_members_profiles`.`name_last`, `t_members`.`msisdn`
FROM `t_course_transaction`
INNER JOIN `t_course_transaction_item` ON `t_course_transaction`.`id` = `t_course_transaction_item`.`id_course_transaction`
INNER JOIN `tm_course` ON `tm_course`.`id` = `t_course_transaction_item`.`id_course`
INNER JOIN `t_course_transaction_payment` ON `t_course_transaction`.`id` = `t_course_transaction_payment`.`id_course_transaction`
INNER JOIN `tm_course_payment` ON `tm_course_payment`.`id` = `t_course_transaction_payment`.`id_course_payment`
INNER JOIN `t_course_payment_wiretransfer` ON `tm_course_payment`.`id` = `t_course_payment_wiretransfer`.`id_course_payment`
INNER JOIN `t_course_payment_status` ON `tm_course_payment`.`id` = `t_course_payment_status`.`id_course_payment`
INNER JOIN `tr_course_transaction_customer` ON `tr_course_transaction_customer`.`id_course_transaction` = `t_course_transaction`.`id`
LEFT JOIN `t_course_member` ON `tr_course_transaction_customer`.`id_course_member` = `t_course_member`.`id`
LEFT JOIN `tm_users_accounts` ON `t_course_member`.`user_id` = `tm_users_accounts`.`id`
LEFT JOIN `t_members` ON `t_members`.`id_user_account` = `tm_users_accounts`.`id`
LEFT JOIN `t_members_profiles` ON `t_members`.`id` = `t_members_profiles`.`id_member`
LEFT JOIN `t_course_transaction_coupon` ON `t_course_transaction`.`id` = `t_course_transaction_coupon`.`id_course_transaction`
WHERE t_course_member.id IS NULL
-- t_course_transaction.record_status != 'DELETE'
-- AND t_course_member.id IS NULL
GROUP BY `t_course_transaction`.`invoice`
ORDER BY `t_course_transaction`.`id` DESC