SELECT
cd.id AS `Client ID`,
CONCAT(cd.firstname,' ',cd.lastname) AS `Client`
FROM hb_client_details cd
JOIN hb_client_access ca ON (ca.id=cd.id)
WHERE (cd.id IN (SELECT client_id FROM hb_accounts WHERE status='Active')
OR cd.id IN (SELECT client_id FROM hb_domains WHERE status='Active')
OR cd.id IN  (SELECT a.client_id FROM hb_accounts a JOIN hb_accounts_addons ad ON (ad.account_id=a.id) WHERE ad.status='Active'))
AND ca.status!='Active'
ORDER BY cd.id ASC