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?

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]