Клиенты без карт... : УКМ-4

SELECT cl.id,
       cl.name,
       cl.sur_name,
       sum( if(c.id IS NULL, 0, if(c.active=0, 0, if(c.deleted=1, 0, 1))) ) active_cards
FROM trm_in_clients cl
LEFT JOIN trm_in_card_client ccl
ON ccl.global_id = cl.global_id AND ccl.client = cl.id
LEFT JOIN trm_in_cards c
ON c.global_id = ccl.global_id AND c.id = ccl.card
WHERE cl.active=1 and cl.deleted=0
GROUP BY cl.id
HAVING active_cards=0
ORDER BY
  cl.id;