> Yes, I know about subqueries, but I want to avoid using it, otherwise
> I'd
> have to add the subquerie in every select from the master table, and I
> don't
> like views also.
> Is there some other way?

pity, maybe you're trying to make this a generic (multi database) solution.
That's a bummer.
Alan

>
> On Thu, Sep 11, 2008 at 10:15 AM, Alan McDonald <alan@...>
> wrote:
>
> > > Hi,
> > > We have a master-detail table which has 3 value fields and a total
> > > which is
> > > calculated by the sum of these 3 fields. I'll just call them
> value1,
> > > value2
> > > and value3 and total.
> > >
> > > Then we have another value field in the detail table, so basically
> the
> > > structure is:
> > >
> > >
> > > Master Table:
> > > Value1
> > > Value2
> > > Value3
> > > Total (calculated by value1 + value2 + value3
> > >
> > > Detail Table:
> > > Value
> > >
> > > The problem is that the Total must always match the sum of the
> values
> > > in the
> > > Detail table.
> > >
> > > We tought about creating a trigger to update the Master Table, but
> as
> > > the
> > > Total is calculated we can't just update that and also we can't
> just
> > > update
> > > one field with the sum of the values from the detail table.
> > >
> > > Another possibility was to create triggers to check if the values
> > > match, but
> > > that also didn't work because when a new value is inserted in the
> > > Detail
> > > table the trigger is run and there is still more values to be
> added, so
> > > basically we are out of ideas.
> > >
> > > Is there any way to assure that the values will always match in
> both
> > > tables?
> > >
> >
> > how many detail records are there? Do you have a foreign key?
> > Have you heard about sub-queries?
> > SELECT FIELD1, FIELD2,
> > (SELECT SUM(FIELDFROMDETAIL) FROM DETAILTABLE WHERE
> > DETAILTABLE.FK=MASTER.ID) AS SUMofdetail
> > FROM MASTERTABLE
> >
> > Alan
> >
> >
> >
>
>
> [Non-text portions of this message have been removed]
>
>
> ------------------------------------
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
>
> Visit http://www.firebirdsql.org and click the Resources item
> on the main (top) menu. Try Knowledgebase and FAQ links !
>
> Also search the knowledgebases at http://www.ibphoenix.com
>
> ++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
> Yahoo! Groups Links
>
>
>