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