how can i filter results when retrieving data in sql using with and union?

The code is performing hierarchical recursion on the clients table. The condition isParent=’False’ seems to suggest you’re attempting to remove non-root rows from the base of the recursion. So I added the condition to the top SELECT statement (above the UNION ALL) only. Also, I removed the unnecessary brackets and reformatted the code for readability (at least imo).

with 
t(clientid, code, cname, rootid, active) as (
    select clientid, code, cname, clientid as rootid, active 
    from clients mt
    where sub = 0 
          and isParent='False'
    union all
    select mt.clientid, mt.code, mt.cname, t.rootid, mt.active 
    from clients mt 
         join t on t.clientid = mt.sub), 
roots(rootid) as (
    select rootid 
    from t
    where cname like '%abb%' 
          or code like '%abb%') 
select t.code, t.cname, t.clientid 
from t 
     join roots rt on rt.rootid = t.rootid 
where t.active='true';