List:General Discussion« Previous MessageNext Message »
From:Paul DuBois Date:November 7 2001 2:53am
Subject:Re: Can this be done in one select
View as plain text  
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.

Thread
.NET and MySQLMichael S Kilaras5 Nov
  • Can this be done in one selectMark Worsdall7 Nov
    • Re: Can this be done in one selectPaul DuBois7 Nov
      • Re: Can this be done in one selectMark Worsdall7 Nov
        • Re: Can this be done in one selectPaul DuBois7 Nov
          • Re: Can this be done in one selectMark Worsdall7 Nov
      • Re: Can this be done in one selectMark Worsdall7 Nov