Hey guys,
I am a little stuck here, i know how to botch a solution together using a
bunch of queries and PHP manipulation, but i am sure there is a more elegant
way to do this one;

i have some tables (i won't put their full defs, just bits that are relevant
(they are huge))

TABLE students
FIELDS
studentID
fname
lname
title
studentNo

TABLE theses
FIELDS
thesisID
studentID
title

TABLE thesis_reports
FIELDS
reportID
thesisID
year


now, what we are dealing with here is records of theses and the reports that
students are required to submit relating to them (yearly). i need to be able
to pull up a record of students who have a record of a thesis (something in
table "theses") but do NOT have an entry in "thesis_reports" for this year
yet.

an attempt at some SQL that sort of pretends to do the right thing :)

SELECT DISTINCT(students.studentID), students.title, students.fname,
students.lname, students.studentNo FROM students, theses, thesis_reports
WHERE theses.studentID=students.studentID AND
thesis_reports.thesisID=theses.thesisID AND thesis_reports.year != '2002'

does that make sense?
what it actually returns is just any student with any record in the DB under
thesis_reports that doesn't eqal '2002', even if they happen to also have
one that *is* for 2002 (ie. my test student has a report for 1999, 2001 and
2002, but still gets selected by that


-- 
Beau Lebens, Technical Officer
Science and Mathematics Education Centre
Curtin University of Technology,
GPO Box U1987 Perth, Western Australia 6845
CRICOS provider code 00301J

t: +61 8 9266-7297 (has voice-mail)
f: +61 8 9266-2503 (ATT: Beau Lebens)
e: [EMAIL PROTECTED]
w: http://learnt.smec.curtin.edu.au/

-- 
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to