'SELECT MAX' subquery problem

From: Andyza <andyza_at_webmail.co.za>
Date: 17 Feb 2005 02:56:52 -0800
Message-ID: <1108637812.430640.153190@g14g2000cwa.googlegroups.com>

My Oracle 9i db table structure is:

Members (sysid(PK), classid, personalid, firstname, surname, dateofbirth, deleted)

Education (qualificationid(PK), personalid(FK), qualificationtype, qualificationtitle, yearobtained)

Terminations (terminationid(PK), sysid(FK), contractstatus, statusdate)

I'm trying to select all the members who are assigned to a particular class (12345) and who have not been removed from that class (I keep a record of students that are removed from each class). The query below returns 0 results when there should be at least 3 (I set up the test data so that there would be a number of 'removed' and 'active' students).

SELECT   m.sysid, m.classid, m.personalid, m.firstname, m.surname,
         m.dateofbirth, e.qualificationtype, e.qualificationtitle,
         e.yearobtained, t.contractstatus,
         TO_CHAR (t.statusdate, 'YYYY/MM/DD') AS statusdate
    FROM members m,
         education e,
         terminations t
   WHERE (m.deleted = 'No')
     AND (    (m.personalid = e.personalid(+))
          AND (m.sysid = t.sysid(+))
          AND (t.contractstatus <> 'Removed')
          AND (m.classid = '12345')
          AND (t.statusdate = (SELECT MAX (t.statusdate)
                                 FROM terminations t
                                WHERE t.sysid = m.sysid))
         )
GROUP BY m.sysid, m.classid, m.personalid, m.firstname, m.surname,
         m.dateofbirth, e.qualificationtype,
         e.qualificationtitle, e.yearobtained,
         t.contractstatus, t.statusdate

ORDER BY LOWER(m.surname) ASC, LOWER(m.firstname) ASC

The 'terminations' table does not contain any records for a student until he/she is 'removed' from the class or 'reassigned' to the class. All students are initially automatically assigned to the class and are members of just this one class.

I think the problem is with the 'SELECT MAX' subquery, but what? Received on Thu Feb 17 2005 - 04:56:52 CST