record selection and left outer join

[princessinthetower]

Crystal 9, Oracle9

I have an individual table linked to a child table {individual.individualid} left outer join to {child.childid}. How can I select individuals who are parents of children over the age of 10 AND individuals without children?

 

[lbass]

For your record selection formula, use:

isnull({child.childID}) or
{child.age} > 10

This would give you a list of childless individuals or parents who had at least one child over age 10. If you only want those parents where ALL children are over age 10, then group by {individual.individualID} and do not use a record selection formula. Instead create a formula {@under10}:

if isnull({child.childID}) or
{child.age} > 10 then 0 else 1

Then go to report->edit selection formula->GROUP and enter:

sum({@under10},{individual.individualID}) = 0

-LB