At 2:38 AM +0000 11/7/01, Mark Worsdall wrote:
>In message <p05100316b80e4570c96c@[192.168.1.31]>, Paul DuBois
><paul@stripped> writes
>>At 1:51 AM +0000 11/7/01, Mark Worsdall wrote:
>>>Hi,
>>>
>>>I have 3 tables
>>>
>>>TABLE columns
>>>student id and name
>>>disability id and name
>>>studentDisability disability_id and student_id
>>>
>>>
>>>I want to extract all students who do not have a disability
>>>assigned to them in the join table.
>>>
>>>SELECT student.name, student.id FROM student WHERE
>>
>>... FROM student LEFT JOIN studentDisability
>> WHERE studentDisability.student_id IS NULL
>>
>
>I do not understand this, sorry, when I tried it, it also returned nothing.
Yes, whoops. Gave you incorrect syntax. Sorry. Try this:
SELECT student.name, student.id
FROM student LEFT JOIN studentDisability
ON student.id = studentDisability.student_id
WHERE studentDisability.student_id IS NULL;
If that doesn't work, try changing IS NULL to <=> NULL and see what
happens. (A recently discovered LEFT JOIN bug prevents some LEFT JOINs
from working with IS NULL that work with <=> NULL.)
>
>So say student table had 5 entrys:
>
>id name
>1 Mark
>2 Andrew
>3 Paul
>4 Fred
>5 Simon
>
>disability table had 6 entrys
>
>id name
>1 SMA
>2 MD
>3 ME
>4 XP
>5 MS
>6 Arthritus
>
>and the join table (studentDisability) had:-
>
>id disability_id student_id
>1 1 1
>2 3 1
>3 3 3
>4 4 3
>5 1 3
>
>What I wanted was it to return:-
>
>id name
>2 Andrew
>4 Fred
>5 Simon
>
>since none of these have any disabilitys.
