Beau,
I've got two words for you - "NOT IN" - I'll even show you where those 2
words go:
SELECT students.studentID, students.title, students.fname, students.lname,
students.studentNo
FROM students, theses
WHERE theses.studentID=students.studentID
AND theses.thesisID NOT IN (SELECT thesisID
FROM thesis_reports
WHERE thesis_reports.year = '2002')
Notice it is 2 queries, 1 finds the thesis_reports done this year and the
other finds all the Student and Theses information. The NOT IN eliminates
the records where the join can be made (which is what you want).
Good Luck and get back to work on your own Theses...
Frank
On 2/20/02 6:46 PM, "[EMAIL PROTECTED]"
<[EMAIL PROTECTED]> wrote:
>
> From: Beau Lebens <[EMAIL PROTECTED]>
> Date: Thu, 21 Feb 2002 09:59:21 +0800
> To: "PHP DB (E-mail)" <[EMAIL PROTECTED]>
> Subject: SELECT where something exists but something else does not
>
> 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
