Hi Adam!

The one alternative I can think of is

select
ta.a,
ta.b,
sum(tb.c) as c
from ta
join tb on ta.a = tb.a
group by ta.a, ta.b
having ta.b <> sum(tb.c)

Though I don't know if this is better or even gets the result you want
(more complex may mean that simple solutions do not exist).

HTH,
Set

Adam wrote:

> Hello Group,
>
> I have some tables that look like this:
>
> ta (a,b); -- (pk a)
> tb (d,a,c); -- (pk d), (fk a -> ta.a)
>
> ta holds some summary records.
> tb holds multiple detail records for each ta record
>
> I need to find out all records of ta where a particular field b is
> different to the sum of certain records in tb.
>
> I have a query that looks like this:
>
> ---
> select
> ta.a,
> ta.b,
> (select sum(tb.c) from tb where ta.a = tb.a) as c
> from ta
> ---
>
> (Don't panic, I am not looking at a denormalised structure, they are
> storing two distinct durations, and I need to identify records with
> different durations for further analysis.)
>
> The real query is a lot more complex with many joins, and the subquery
> is also more complex. I want to find the records where field b and c
> differ:
>
> ---
> select
> ta.a,
> ta.b,
> (select sum(tb.c) from tb where ta.a = tb.a) as c
> from ta
> where ta.a <> (select sum(tb.c) from tb where ta.a = tb.a)
> ---
>
> But this takes a double hit to b.
>
> PLAN (B INDEX (FK_B_A))
> PLAN (B INDEX (FK_B_A))
> PLAN (A NATURAL)
>
>
> Alternatively, I could define a view:
>
> ---
> create view v (a,c) as
> select tb.a, sum(tb.c)
> from tb
> group by tb.a;
> ---
>
> and then run the query
>
> ---
> select
> ta.a,
> ta.b,
> tb.c
> from ta
> join tb on (ta.a = tb.a)
> where ta.b <> tb.c
> ---
>
> But I don't want to use a view in this case. I am using Firebird 1.5,
> so derived table are not an option. Is there any alternatives asides
> from a view, stored procedure or client side filtering?
>
> Adam