How do I create Master/Detail between SQLTables? 1

[delphiman]

Can anyone please tell me how to create a Master/Detail
relationship between two Tables in MyDataBase.gdb which have a common field P_NO between them?

In MyDatBase.gdb I have one table called "MST" hooked to TIBDataSet1 and another table called "DET" hooked to TIBDataSet2. Each are hooked to a grid.

In the SelectSQL Property of TIBDataSet2 I have the following code

SELECT *
FROM DET t2, MST t1
WHERE t2.P_NO = t1.P_NO.

But moving the pointer in the MST-grid has no effect on the DET-grid whilst I am expecting to see ONLY those records in DET-grid where the P_NO = P_NO pointed to in MST-grid.

What am I missing?

 

[delphiman]

PROBLEM SOLVED. But ...I still need a bit of other help.

Firstly
Thanks a million to Steve Van Els for his un-ending patience and interest in this thread - and behind the scenes.

[b}The problem[/b]
To have a Master/Detail relationship between 4 tables called
PARENT (where P_NO is the father number.)
CHILD (where P_NO is the father number and C_NO is the child number.)
GCHILD (where P_NO is " " C_NO is " " and GC_NO is the grand child number.)
GGCHILD (where P_NO is " " C_NO is " " GC_NO is "" and GGC_NO is the grt gchld number.)

When pointing to (say) father 2:

We do NOT want
1.
To see ALL the children in the database when pointing at (say) father 2 in PARENT.

2.
Child 1 of (say) Father 2 to be Child 3 - because Father 1 has 2 children - which WILL
happen if one indexes on C_NO, GC_NO and GGC_NO. (Thus making them unique. So that any
attempt at entering "1" for Child 1 of Father 2 will result in a key-violation.)

We DO want:
To see the all the Children (GChildren, GGChildren) of that father ONLY.
Child 1 for EACH father - MUST obviousely be "Child 1" in each case.

The following codes will NOT do the trick.

Select * from CHILD
where P_NO = P_NO

NOR WILL

Select *
from GGCHILD t4, GCHILD t3, CHILD t2, PARENT t1
WHERE (t4.GGCHILDGC_NO = t3.GCHILDGC_NO) AND (t3.GCHILDC_NO = t2.CHILDC_NO) AND (t2.CHILDP_NO = t1.P_NO)

...as these merely result in ALL the Children in CHILD who have a father in PARENT becoming available in the first instance - as will any GGCHILD who has relatives in
the other tables in the second instance.

I have solved the Master/Detail problem as follows:

A.
Table CHILD has a field CAKV. (For Child anti-key-violation if you like. ) So that
we DO have a unique field in the table. Whilst GCHILD has GAKV and GGCHILD has
GGAKV for the same reason.

B.
I have another table (called TRNSFR) in which I have four fields
being P_NO, C_NO,G_NO and GG_NO - to correspond with similar fields in tables
PARENT (where P-NO is the number of each father.)
CHILD (where C_NO is the child number of each father.)
GCHILD (where GC-NO is the grand-child number for each child.)
GGCHILD (where GGC-NO is the grand-child number for each child.)

This table must NEVER have more than ONE record in it - to obviate the problem
described above.

C.
In the event-handler of AfterScroll property in IBDataSet (which I am using) of PARENT I post the value of P_NO into TRNSFRP_NO. In the same manner I post
the values of C_NO, GC_NO into TRNSFRC_NO and TRNSFRGC_NO.

The following code then produces a perfect Master/Detail result for the Father to GreatGrandChild relationhsip.

Select *
from GGCHILD t1, TRNSFR t2
WHERE (t1.GGCHILDGC_NO = t2.TRNSFRGC_NO) AND (t1.GCHILDC_NO = t2.TRNSFRC_NO)
AND (t1.CHILDP_NO = t2.TRNSFRP_NO)

BUT ...

AA
I need to cause CAKV, GAKV and GGCHILD to AUTO-INCREMENT by 1 upon each new record being created. Something I still don't how to get to work.

(In the model I did this with manual entries.)

BB
I need the code required at AfterScroll to post the Value of P_NO (etc.) into TRNSFR - bearing in mind that it is a table in an Interbase DATABASE called PCHild.gdb.

Whilst I am familiar the code required to post to a field in a table (using Paradox Tables) I don't know the code to use to do it when using an Interbase DATABASE with a table in it.

Anyone?

 

[delphiman]

Thanks for all your trouble Steve - and the model which you sent back via other e-mail.

Unfortunately (and with profound respect!) it appears you have missed a vital point -
which is that C_NO CANNOT be unique (as you have it). Because this will result in the
first child of "Father 2" being numbered "3" - if Father 1 already has two children in
the CHILD table who are (obviously) already numbered "1" and "2".

The same applies to the Child/GChild relationship.

THAT is why I used CAKV (GCAKV and GGCAKV) as highly necessary unique fields in tables
CHILD, GCHILD and GGCHILD to prevent key-violations.

Bit tricky eh.

(At this point I might mention that the following doesn't work either:

select *
from CHILD
inner join PARENT P_NO
on (CHILD. P_NO = PARENT. P_NO))

It seems the only way to solve this problem remains as I have outlined on May 23rd.
Which (interalia) allows one to have a
Father/Child/GChild/GGChild/GGGChild/GGGGChild/GGGGGChild .... all the way back to
Adam and Eve.

PROVIDED that one enters the values of P_NO (C_NO, GC_NO) MANUALLY via the IBConsole -
which is obviousely not a practical option.

So that it becomes essential that one MUST be able to "Post" the value of P_NO
(and C_NO, GC_NO. etc) into TRNSFR as explained there. (Also refer to thread102-280556.)

I find it astonishing that after some two weeks I could not find (from any source
whatsoever - including from any of some four different threads elsewhere) HOW to
copy data from the field in one .GDB table to another field in another table of the
same DataBase. Frustration which is amplified by the fact that it is (after all) such an
OBVIOUS thing to want to do.

It is quite unbelievable that NOWHERE in Delphi Help and/or a PILE of exotic
(expensive) books by authors of some renown on Delphi NONE of them handle this. :-(

After a lot of head-banging I finally cracked it as follows:

procedure TParentDM.IBFatherDataSetAfterScroll(DataSet: TDataSet);
begin
IBdsTrnsfr.Active := True;
if IBdsTrnsfr.state in dsEditModes then
else
IBdsTrnsfr.edit;
if IBdsTrnsfrP_NO.value = 0 then
IBdsTrnsfrP_NO.value := 1
else
IBdsTrnsfrP_NO.value := IBdsChildP_NO.value;
end;

This, if applied with my comments in this thread on May 23rd, produces the required Master/Detail/Detail/Detail relationship.

The case isn't closed yet though because (with the above) one has a delayed action whereby the CHILD of FATHER 2 is only displayed after one scrolls TWICE in FATHER.

I'll keep this thread posted on how I solve it.

So much for the problem referred to as "BB" above.

The problem referred to in "AA" is handled by clicking in the GeneratorField Property in the IBDataSet. Where what is required becomes obvious.