Subject Re: [IBO] joining tables with one to many relationships
Author Helen Borrie
At 01:38 PM 18/06/2004 +0000, you wrote:
>I have contemplated building a table with several joins, 2 of them
>one to
>many relationships. However, conceptually, I have not been able to
>out how to handle the "many" component...for example...there is a
>table (including address and social security number) and a phone
>table...they are both on one present, i have a separate
>query for
>both and it works fine. However, it would be leaner and meaner if I
>join the two and still be able to add multiple phone numbers to the
>table...i understand that i would have to write my own insert/edit sql
>statements in the TIB_Query editor for that issue, but will firebird
>multiple row inserts for one part of a joined table, while handling a
>row insert for the master part of the master-detail relationship?

With regular I/U/D statements, no. SQL doesn't permit you to operate on
multiple tables in a single statement.

There are two ways to make joined sets updatable with IBO. If you need to
update multiple tables, write parameterised stored procedures for the
Insert, Update and Delete operations and place EXECUTE PROCEDURE calls in
the dataset's InsertSQL, EditSQL and DeleteSQL properties. Make the
tables' keys the parameters. Take care to get perfect KeyLinks.

If you only want to update one table, set the KeyRelation property to the
name or alias of the table and, again, set perfect KeyLinks, and the normal
Insert, Edit and Delete methods will work.

To do what you're describing above, you won't want joined tables, as you
will get many duplicated fields from the "many" relationship. Use tiered
master-detail setups for this sort of thing.