At 11:07 AM 24/06/2004 +0200, you wrote:

>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.

Joined queries are not restricted to two tables: there can be many.

select
r.reservationid,
sum(p.amount),
sum(d.amount)
from reservation r
join payment p on p.reservationid = r.reservationid
join debt d on d.reservationid = r.reservationid
/* where d.amount > ? */
group by r.reservationid
/* having sum(d.amount) > ? */

I included both WHERE and HAVING clauses here because I wasn't clear which
criterion you were after.

>Then I tried to join the two selects this didn't work.

Not sure what this means...

>Then I tried stored procedures:
>
>CREATE PROCEDURE calcDebtsForRelation(objectid integer)
>RETURNS( debt DECIMAL(18, 3))
>AS
>BEGIN

FOR

> select sum(d.amount) from reservation r
> join debt d on d.reservationid = r.reservationid
> where r.reservationid = :objectid
>
> INTO :debt

DO

> 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)

Use FOR SELECT...INTO....DO....SUSPEND to return multiple row sets.

/heLen