select brightmail.report_sender.*,
case brightmail.sender_group.group_type
    when 1 then 'bad sender'
    when 2 then 'good sender'
end as sender_status ,brightmail.sender_group.sender_type
from brightmail.report_sender
left join brightmail.report_sender_ip_address
on brightmail.report_sender.report_sender_ip_address_id = brightmail.report_sender_ip_address.id
left join brightmail.report_sender_alias
on brightmail.report_sender.report_sender_alias_id = brightmail.report_sender_alias.id
left join brightmail.report_sender_domain
on brightmail.report_sender.report_sender_domain_id = brightmail.report_sender_domain.id
left join brightmail.sender_group
on brightmail.sender_group.sender_name = brightmail.report_sender_ip_address.addr
or brightmail.sender_group.sender_name = concat(brightmail.report_sender_domain.name,'@',brightmail.report_sender_domain.name);