2 TABLE query mistake HELP!

krei

i'm trying to display subjects NOT taken by a particular student..

i have 3 tables: student, subject and studsub
each table has the following fields:

STUDENT
-id (PK), fname, lname, mi

SUBJECT
-code (PK), desc, units

STUDSUB
-studid (FK), subcode (FK), subdesc, grade

So for example my subject table has:

CODE
ICT101
ICT102
ICT103
ICT104
ICT105

and my studsub table has

STUDID SUBCODE
001 ICT101
001 ICT102
002 ICT103
002 ICT101
003 ICT105

so now i would like to see the subjects NOT TAKEN by student 001:
the output should be
ICT103
ICT104
ICT105

I used this code:

$query = "SELECT subject.desc, subject.code ".
"FROM subject JOIN studsub ".
"ON studsub.studid = $id ".
"WHERE subject.code = studsub.subcode";

$result = mysql_query($query) or die(mysql_error());

// Print out the contents of each row into a table
while($row = mysql_fetch_array($result)){
echo $row['code']. " - ". $row['desc'];
echo "<br />";
}

But it went out all wrong.
it displayed the subjects of the other students too :queasy:

PLEASE HELP!!!

toma42

You need a query in this form:

SELECT fname, lname, code
FROM student
LEFT OUTER JOIN studsub ON (id = studid)
LEFT OUTER JOIN subject ON (code = subcode)
WHERE subcode is null;

You should probably write your query on the command line before putting in PHP for ease of editing.

krei

toma42;10973542 wrote:
You need a query in this form:

SELECT fname, lname, code
FROM student
LEFT OUTER JOIN studsub ON (id = studid)
LEFT OUTER JOIN subject ON (code = subcode)
WHERE subcode is null;

You should probably write your query on the command line before putting in PHP for ease of editing.

owh! Thanks!

That worked, but the query was supposed to display the SUBJECT CODE and DESCRIPTION that a selected student isn't taking.

I guess i'll just toggle this query which you gave.

THANKS A LOT 🙂