Re: query problem with multiple tables

}From: ericd@cats.ucsc.edu (Eric D Davis) }Subject: query problem with multiple tables }Date: 11 Dec 1993 00:11:36 GMT }X-Informix-List-Id: <news.5063> } }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? Yes. Select the Pupil ID too. You should use ORDER BY Pupil.Name, Pupil.ID, Class.Name Use BEFORE and AFTER GROUP OF Pupil.ID. After all, you need a new output when the Pupil.ID changes, not when the name changes, if you are allowing duplicate names (which you undoubtedly have to do. Yours, Jonathan Leffler (johnl@informix.com) #include <disclaimer.h>