How to create Master/Detail/Detail/Detail?

[delphiman]

I use Interbase 6 SQLServer in conjunction with Delphi 6 Enterprise.

I have a FATHER (Master), CHILD (Detail), GCHILD (Detail), GGCHILD (Detail) relationship.

I find (Example 1) below works perfectly. But Examples 2 and 3 do not.

Can someone please tell me what I am doing wrong?

Example 2 provides any and all GRANDCHILDREN who have a join to a CHILD and FATHER.
Example 3 provides any and all GREATGRANDCHILDREN who have a join to a GRANDCHILD, CHILD
and FATHER.

which is (of course) ALL of them and is not what I want.

What I want is:
In Example 2 when POINTING to (say) FATHER 2 and (his) CHILD 3 at any given moment I want
ONLY the GRANDCHILDREN of FATHER 2 and CHILD 3.
Equally When POINTING to (say) FATHER 3, (his) CHILD 2 and GRANDCHILD 1 at a given moment
I want to see ONLY the GREAT-GRANDCHILDREN of FATHER 3, CHILD 2 and GRANDCHILD 1.

EXAMPLE 1

select *
from CHILD t1, FATHER t2
where t1.P_NO = t2.P_NO

EXAMPLE 2

select *
from GCHILD t1, CHILD t2, FATHER t3
where (t1.P_NO = t3.P_NO) AND (t1.C_NO = t2.C_NO)

EXAMPLE 3

select *
from GGCHILD t1, GCHILD t2, CHILD t3, FATHER t4
where (t1.P_NO = t4.P_NO) AND (t1.C_NO = t2.C_NO) AND (t1.GC_NO = t3.GC_NO)

Thanks in advance

 

[svanels]

About the master-detail-detail thing I did some coding.
your tables do have redundant information.

I used the following

table Fathers --> P_NO, Name P_NO = key field and generated
table Children ---> C_NO, P_NO, NAME C_NO = key field and generated P_NO = look up
table GrandChildren ---> GC_NO, C_NO, NAME GC_NO = key field and generated C_NO = look up

etc.......

table GrGrandChildren---> GGC_NO, GC_NO, NAME GGC_NO = key field and generated GC_NO = look up



Each table has his generator and trigger P_NO_GEN, C_NO_GEN & GC_NO_GEN

In the dataset for the fathers:
1) Adjust the generatorfield property to --> P_NO_GEN
2) Set the InsertSQL property to ---> insert into father (name) values name)


In the dataset for the child:
1) Adjust the generatorfield property to --> C_NO_GEN
2) Set the InsertSQL property to ---> insert into child (name, P_NO) values name, _NO)
3) Set the DeleteSQL property to --->delete from CHILD where C_NO = :OLD_C_NO

I had to put in a dbNavigator for each dataset to get the code executed, but this can be transferred to a button.

Hope this gives you a clue

regards
Steven van Els
SAvanEls@cq-link.sr

 

[svanels]

OOPs

The various selectSQL statements are:

select * from FATHER

select C_NO, NAME, P_NO from CHILD
where P_NO =_NO

select C_NO, GC_NO, NAME from GRANCHILD
where C_NO =:C_NO

Regards Steven van Els
SAvanEls@cq-link.sr

 

[delphiman]

Refer my thread102-272955.