How to compare between two values in one field depending on another field in SQL* / Oracle Reports?

Question

I have three fields in a table named table_family.

One is "relation" it has 3 kinds of values 1 for person, 2 for parents, 3 for son.

Other field is "age" and it contains the ages of all the relations in the first table.

The 3rd field is for the family number.

Now the relation could be repeated like four sons and two parents for the same family This is an example:

Family   Relation   Age

1         1          25

1         2          40

1         2          35

1         3          30

1         3          28

1         3          29

2         1          30

2         2          40

2         3          12

Now I want to build a report that displays an error which is: Whenever the difference between parent (relation=2) and son (relation=3) is less than 12, then this is an error.

I tried this code :

select family from table_family
group by family
having
SELECT  family

FROM table_FAMILY 

GROUP BY 

     family
HAVING
((
 MAX(DECODE ( relation,2,age ))  
-  MAX(DECODE ( relation,3,age ))
 ) 
 <12 )
 OR
 (
 (SELECT MIN(age) FROM table_FAMILY WHERE relation=2) -(SELECT MIN(age) FROM       table_FAMILY WHERE relation=3) < 12

 )

If I use this code then only the maximum and minimum values will be compared. The values in between will be ignored but I need them to be compared in the same way.

The output report will be something like:

family  difference between parent and son

1               10

1               12

1               11

1                5

1                7

1                6

This what I need in the final report

For family = 1 I subtracted parent age - son age =

40-30=10

40-28=12

40-29=11

35-30=5

35-28=7

35-29=6

But for the 2nd family

40-12= 28 >12 then should not be listed (not an error).

Any help please?

Answer 1

Something like this maybe?

select *
from table_family f1 join table_family f2 on f1.family = f2.family
where f1.relation = 2 and f2.relation = 3
 and f1.age - f2.age <= 12