sql select puzzle: remove children when parent is filtered out

I have a table essentially:

name         has_children      parent_id    row_id values0.....valuesn
parent       1                 1            1
children     0                 1            2
children     0                 1            3
parent       0                开发者_如何学C 4            4
parent       1                 5            5
children     0                 5            6
children     0                 5            7

the values for the children can be different than the values for the parent. i want some selects/joins that will filter the table on a value column (i.e. >10) and will return the parent (even if false for the filter) if one of it's children is true for the filter.

acceptable return:
parent=true    all children=false, return just parent
parent=false   >=1 children=true, return parent and all non-filtered child

i'm sure this has been thought about before but i don't have the faintest idea how to phrase the question to find a solution.

ANSI compliant. Each specific DBMS may have a faster implementation

select *
from tbl
where id in-- PARENTS of CHILDREN that match
(   select parent_id from tbl
    where values0 > 10 and has_children = 0)
or id in   -- ONE CHILD ONLY
(   select MIN(id) from tbl
    where values0 > 10 and has_children = 0
    group by parent_id)
or id in   -- PARENTS
(   select id from tbl
    where values0 > 10 and has_children = 1)

Better written as a JOIN

select t.*
from 
(   select parent_id as ID from tbl
    where values0 > 10 and has_children = 0
    UNION
    select MIN(id) from tbl
    where values0 > 10 and has_children = 0
    group by parent_id
    UNION
    select id from tbl
    where values0 > 10 and has_children = 1) X
join tbl t on X.ID = t.ID

It is probably easiest to deal with this as two separate queries with a UNION between them.

1. Select the parent where all children are false under condition.
2. Select the parent and some child (MAX or MIN is probably easiest).