Henk,

I'm not a Firebird expert, but I think I have come across a similar
situation in SQL. Think about it this way:

We can join reservation to debt and total debt:

select A.SOMEFIELD, sum(AMOUNT)
from reservation A join debt B on B.RESERVATIONID = A.RESERVATIONID
group by RESERVATIONID.

This returns a row for each reservation in which there is any corresponding
debt row.

Likewise

select A.SOMEFIELD, sum(AMOUNT)
from reservation A join payment B on B.RESERVATIONID = A.RESERVATIONID
group by RESERVATIONID.

gets us the sum of payments.

But if we try to do the triple join, we finish up with an unrestricted join
of debt X payment, and I don't think there is any way to restrict that so
there is only one row per reservation.

I solved a similar problem with select subqueries (which are allowed by
Firebird but not by the BDE) which for your case should look like this:

select A.RESERVATIONID,
(select sum(B.AMOUNT)
from debt B
where B.RESERVATIONID = A.RESERVATIONID ) Debt,
(select sum(C.AMOUNT)
from payment C
where C.RESERVATIONID = A.RESERVATIONID ) Payments
having Debt - Payments > :x

hth

Walter


At 03:30 PM 6/24/2004 +0200, you wrote:

>Sorry if this is a repost. I waited several hours.
>
>
>Hi,
>
>I've been stuck getting a query running for a few days now. Clean somebody
>help me please.
>The problem is this.
>
>Suppose I have 3 tables:
>
>create table reservation
>(
> reservationid integer not null,
> primary key (reservationid)
>)
>
>create table debt
>(
> debtid integer not null,
> reservationid integer,
> amount decimal (18, 3),
> primary key (debtid)
>)
>
>create table payment
>(
> paymentid integer not null,
> reservationid integer,
> amount decimal (18, 3),
> primary key (paymentid)
>)
>
>And this as testdata:
>
>insert into reservation values(1);
>insert into reservation values(2);
>insert into reservation values(3);
>insert into reservation values(4);
>
>insert into debt values(1,1,100);
>insert into debt values(2,2,200);
>insert into debt values(3,3,300);
>insert into debt values(4,4,400);
>insert into debt values(5,1,10);
>insert into debt values(6,2,20);
>insert into debt values(7,3,30);
>insert into debt values(8,4,40);
>
>insert into payment values(1,1,10);
>insert into payment values(2,2,20);
>insert into payment values(3,3,30);
>insert into payment values(4,4,40);
>insert into payment values(5,1,20);
>insert into payment values(6,2,40);
>insert into payment values(7,3,60);
>insert into payment values(8,4,80);
>
>
>I would like to have a result from a select that is:
>Column 1: reservationid
>Column 2: total debt for a reservation id
>Column 3: total payment for a reservation id
>Column 4: total debt-payment for a reservation id
>
>I also would like to be able to use a where clause to select reservationid's
>where the amount due is larger then x.
>
>My first attempts where using selects like:
>
>select r.reservationid, sum(p.amount) from reservation r
>join payment p on p.reservationid = r.reservationid
>group by r.reservationid
>
>this gives:
>reservationid sum
>1 30
>2 60
>3 90
>4 120
>
>But I was not able to combine this with the debts.
>
>Then I tried to join the two selects this didn't work.
>
>Then I tried stored procedures:
>
>CREATE PROCEDURE calcDebtsForRelation(objectid integer)
>RETURNS( debt DECIMAL(18, 3))
>AS
>BEGIN
> select sum(d.amount) from reservation r
> join debt d on d.reservationid = r.reservationid
> where r.reservationid = :objectid
>
> INTO :debt;
> SUSPEND;
>END
>
>I could use the stored procedure like select * from calcDebtsForRelation(1).
>What is wanted was something like:
>select * from reservation r, calcDebtsForRelation(r.reservationid)
>
>expecting it to give:
>1 110
>2 220
>3 330
>4 440
>
>But it gives: not current record for fetch operation on line 1: select *
>from reservation r, calcDebtsForRelation(r.reservationid)
>
>Now I am stuck.
>
>Could somebody please help.
>
>Thank you,
>
>Henk van der Meer.
>
>
>
>
>Yahoo! Groups Sponsor
>ADVERTISEMENT
>
>
>----------
>Yahoo! Groups Links
> * To visit your group on the web, go to:
> *
> <http://groups.yahoo.com/group/firebird-support/>http://groups.yahoo.com/g
> roup/firebird-support/
> *
> * To unsubscribe from this group, send an email to:
> *
> <mailto:firebird-support-unsubscribe@yahoogroups.com?subject=Unsubscribe>f
> irebird-support-unsubscribe@yahoogroups.com
> *
> * Your use of Yahoo! Groups is subject to the
> <http://docs.yahoo.com/info/terms/>Yahoo! Terms of Service.

/*----------------
C. Walter Ogston
ogstoncw@... Kalamazoo, Michigan
*/