The old sub category query in one problem

[azz0r_wugg]

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)

[guido2004]

No need for that WHERE, you already got only the rows where ct.id is NULL or ct.parentid = d.id

Does this query give you what you need?
If not, what's wrong with it?

[azz0r_wugg]

It only returns one row, which is the one in Client_Tree, I want Client results which arent in Client_Tree aswell.

If I remove COUNT(ct.id) as ct_count from the field list I get a better result, but then I don't have any count for the children

[guido2004]

Get rid of the DISTINCT, and use a GROUP BY instead.
And you should still get '1' as a result of the COUNT for rows that are not present in the Client_Tree table.

If so, try this:

SELECT 
    d.*
  , SUM(CASE WHEN ct.id IS NULL THEN 0 ELSE 1 END) AS ct_count
FROM Client as d 
LEFT JOIN Client_Tree as ct 
ON ct.parent_id = d.id
GROUP BY d.id

[azz0r_wugg]

Great! The only issue now is that the child row comes through in the results, aka a row in Client_Tree with child_id = d.id, means that "d" row shouldnt appear in the list as its going to be put under the parent client

Another field sub query?

[guido2004]

Another left join of the Client_tree table, this time on child_id, and the result must be NULL (the client must not present in the client_tree table as a child):

SELECT 
    d.*
  , SUM(CASE WHEN ct.id IS NULL THEN 0 ELSE 1 END) AS ct_count
FROM Client as d 
LEFT JOIN Client_Tree as ct 
ON ct.parent_id = d.id
LEFT JOIN Client_Tree as ct2 
ON ct2.child_id = d.id
WHERE ct2.id IS NULL
GROUP BY d.id

[azz0r_wugg]

Thanks! Fantastic