query problem with multiple tables
Here's a sample application for you, with the
problem described later. This is for SE and ACE.
I have a school schedule database, with the following tables:
- Pupils
ID
Name
- Classes
ID
Class Name
- X-Ref
Pupil ID
Class ID
To run a report of all students enrolled in all classes,
I would select all the entries from the x-ref table, and
sort them as needed, like so:
select xref.pupil, xref.class, pupil.name, class.name
from pupils, classes, xref
where xref.pupil = pupil.id and
xref.class = class.id
order by pupil.name, class.name
The output I want is as follows:
Doe, John
Informix 101, Informix 102
Class Total: 2
Doe, Jane
Informix 201, Informix 202, Informix 203
Class Total: 3
So I setup my ace report as follows:
BEFORE GROUP OF pupil.name
PRINT pupil.name
ON EVERY ROW
PRINT class.name, ", ";
AFTER GROUP OF pupil.name
PRINT "Class Total: ", count
Here's the problem we have:
What if I have two students with the exact same name?
The BEFORE/AFTER GROUP will not differentiate between the
two identical names - the BEFORE will print the information
from the first record and the AFTER will print the information
from the last!
Informix tech support designed the above select statement,
because we had these nice, pretty tables. Unfortunately,
they can't seem to understand the problem described above.
Any ideas?