I've used views for this kind of problems. What's the problem with a view?

On Thu, Sep 11, 2008 at 3:20 PM, Fabio Gomes <fabioxgn@...> wrote:
> 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]
>
>