Два With

with Copy (pID) as 
(select pID from program where ParentID  = 1 )
with Deleted (pID) as (select pID from program where deleted = 1 ) 
select *  from program  where pID IN (SELECT pID FROM Copy) AND pID NOT IN (SELECT pID FROM Deleted)

;with 
    Copy (pID) as (
        select pID from program where ParentID  = 1 
    )
    , Deleted (pID) as (
        select pID from program where deleted = 1 
    ) 
select *  from program  where pID IN (SELECT pID FROM Copy) AND pID NOT IN (SELECT pID FROM Deleted)