Table 1: Client
id, name, email
Table 2: Client_Tree
id, parent_id, child_id
What I'd like todo is have a single query that gets Client rows without a Client_Tree row or Client rows with a Client_Tree row but only parent_id as their Client.id
I'd also like todo a count on that query for Client_Tree rows with them as the parent_id.
So then, if the count is higher than one I can loop the results and bind the child rows.
So far I have
SELECT DISTINCT d.*, COUNT(ct.id) as ct_count
FROM Client as d
LEFT JOIN Client_Tree as ct ON (ct.parent_id = d.id)
WHERE (ct.id IS NULL OR ct.parent_id = d.id)