I think I have solved your problem...
I have attached an sql file to create the sample DB I created for my test
and a PHP file for you to run to see how I have implemented it.
First create the tables with the .sql file an then execute the .php file
from your browser.
Hope these help.
----- Original Message -----
From: "Beau Lebens" <[EMAIL PROTECTED]>
To: "PHP DB (E-mail)" <[EMAIL PROTECTED]>
Sent: Thursday, February 21, 2002 3:59 AM
Subject: [PHP-DB] 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
> way to do this one;
> i have some tables (i won't put their full defs, just bits that are
> (they are huge))
> TABLE students
> TABLE theses
> TABLE thesis_reports
> now, what we are dealing with here is records of theses and the reports
> students are required to submit relating to them (yearly). i need to be
> to pull up a record of students who have a record of a thesis (something
> table "theses") but do NOT have an entry in "thesis_reports" for this year
> 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
> 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
> 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
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php