sql select puzzle: remove children when parent is filtered out
- by knicnak32
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 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 one 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.
