A puzzle, help!

[Yuki]

Link to create and populate script: http://shahinrostami.com/webdevass2/draft.txt

 

Problem is this:

There are modules, there are tutors.

Tutors teach modules.

 

A module can be taught by more than one tutor, so there are three tables.

 

Tutor (tutorID and their info)

Module(moduleCode and its info)

Teaching(tutorID, moduleCode)  this is a composite key table

 

 

I have a combo box which I fill up with module names, I'm trying to get it to only list those that the tutor ISNT teaching.

 

 

It's not as simple as

 

SELECT module.module_Code, module.module_Name

FROM teaching

RIGHT JOIN module

USING (module_Code)

WHERE tutor_ID !=90000013

GROUP BY module.module_Code;

 

 

 

because of course another tutor may be teaching that module also, so the module is still accounted for. Also this only returns the modules that ARE being taught by someone. If the module currently has no tutors (null after right join) it doesn't include them!

 

help!

[mwasif]

Use LEFT JOIN

SELECT module.module_Code, module.module_Name
FROM module 
LEFT JOIN teaching
USING (module_Code)
WHERE tutor_ID !=90000013
GROUP BY module.module_Code;

[Yuki]

That's not what I'm after

 

 

I need to return the list of modules the teacher does not teach.

 

 

Their are null values in the join, so I need a right join, there are also OTHER tutors with module codes in TEACHING

 

So if it finds

 

63PR2201  9000013

63PR2201  9000011

 

 

it'll != the 9000013 but include 9000011 in the results, see my problem?

 

 

 

 

 

I'm stuck

[Yuki]

$modulelist = 'SELECT module.module_Code \'module_Codes\', module.module_Name \'module_Name\''

        . ' FROM teaching'

        . ' RIGHT JOIN module'

        . ' USING (module_Code)'

       

        . ' WHERE tutor_ID is null or tutor_ID !=' . $tutor . ''

        . ' GROUP BY module.module_Code;';

 

 

 

Here's what I got so far.

 

 

This shows ALL modules that the TUTOR is not on AND the ones he is (because other tutors are on them)

 

 

Someone must have a big brain hooked up to other brains!

 

[Barand]

SELECT m.module

FROM module m

LEFT JOIN teaching t ON m.module_id = t.module_id AND t.tutor_id=$tutor

WHERE t.tutor_id IS NULL

[Yuki]

Brilliant, thank you Barand, second time you've helped me out, problem solved!

 

SELECT m.module_Code, m.module_Name

FROM module m

LEFT JOIN teaching t ON m.module_Code = t.module_Code AND t.tutor_ID= 90000001

WHERE t.tutor_ID IS NULL;