Hello,

I have a table that keeps information in a kind of sub-records of other records in the same table.
Each record has an IDKey and a RelationKey. In the RelationKey the number of the parent record (in the same table!) is stored, if the RelationKey = 0 the record indicates a parent node without having other parents above. (I hope I have successfully explained this!)

So the data could look like:

IDKey   RelationKey   Success
   1             0     Y
   2             1     N
   3             1     Y
   4             2     y
   5             4     Y

Now I need to select all records that have the field "Success" = "Y", but ONLY those where also ALL parents have Success = "Y".

I would like to make an inner join or so, but the problem is that I don't know how many subrecords will be there. In this case, record 5 should not be selected, although record 4 (the parent of 5) is set to "Y", because record 2 (the parent of 4) is set to "N".
How can I solve this problem?

Thanks for your suggestions.