Hi David,

David ha scritto:

> $Q1 = ibase_query ("select distinct(SERVE_DATE) from TRANS t inner join STUDENT s on t.STUD_NO=s.STUD_NO where t.GENE=0 and t.ORDERED=1 $AndFromDate $AndToDate order by SERVE_DATE");
>
> $Q2 = ibase_query ("select distinct(AM_ROOM) from STUDENT");
>
> $Q3 = ibase_query ("select t.STUD_NO, s.LAST_NAME , s.FIRST_NAME, s.GRADE from TRANS t inner join STUDENT s on t.STUD_NO=s.STUD_NO where t.GENE=0 and t.ORDERED=1 and s.AM_ROOM='$R2[0]' and t.SERVE_DATE='$R1[0]' $AndFromDate $AndToDate group by t.STUD_NO, s.LAST_NAME, s.FIRST_NAME, s.GRADE, t.SERVE_DATE order by t.SERVE_DATE, s.LAST_NAME, s.FIRST_NAME");
>
> $Q4 = ibase_query ("select DESCRIPTION, sum(QUANTITY) from TRANS where STUD_NO=$R3[0] and GENE=0 AND ORDERED=1 and SERVE_DATE='$R1[0]' group by DESCRIPTION order by DESCRIPTION");
>
>

I'm not sure, perhaps you can obtain what you need using Firebird 2.1,
with a COMMON TABLE EXPRESSION

http://www.firebirdsql.org/rlsnotesh/rlsnotes210.html#rnfb210-cte

and/or DERIVED TABLES

http://www.firebirdsql.org/rlsnotesh/rlsnotes210.html#rnfb20x-dml-derived-tables

Using only DERIVED TABLES, you don't need $q1 and $q2, because
conmditions and tables are anyway in $q3.
I'm not sure about GROUP BY in $q3: what are you tryinng to do?
Something like (writing without testing)

select t.STUD_NO, s.LAST_NAME , s.FIRST_NAME, s.GRADE, t.SERVE_DATE, R.DESCRIPTION, R.QTY
from
(TRANS t inner join STUDENT s USING (STUD_NO))
join
(SELECT STUD_NO, GENE, ORDERED, SERVE_DATE, DESCRIPTION, sum(QUANTITY)
from TRANS
group by 1, 2, 3, 4, 5
) AS R (STUD_NO, GENE, ORDERED, SERVE_DATE, DESCRIPTION, QTY )
USING ( STUD_NO, GENE, ORDERED, SERVE_DATE )
where t.GENE=0 and t.ORDERED=1 $AndFromDate $AndToDate
order by t.SERVE_DATE, s.LAST_NAME, s.FIRST_NAME, description

Ciao.
Mimmo.