SitePoint Sponsor

User Tag List

Results 1 to 2 of 2

Thread: Left Join help

  1. #1
    SitePoint Member
    Join Date
    Mar 2004
    Location
    India(Currently working in Malaysia)
    Posts
    2
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Left Join help

    Hi,
    I'm facing a weird problem in left join.
    I've three tables
    1. Student_Detail_Profile - contains student informations..
    2. Student_Courses - contains Courses taken by students
    3. Stud_Assignment - Assignment details of the student


    Student_Detail_Profiel Structure
    Field,Type,Null,Key,Default,Extra
    StudentID,varchar(15),,PRI,,
    SFirstName,varchar(40),YES,,NULL,
    SLastName,varchar(40),YES,,NULL,
    SAddress1,varchar(150),YES,,NULL,
    SAddress2,varchar(100),YES,,NULL,
    City,varchar(50),YES,,NULL,
    StateID,int(10) unsigned,YES,,NULL,
    CountryID,int(10) unsigned,YES,,NULL,
    SPostcode,varchar(8),YES,,NULL,
    SPhone,varchar(20),YES,,NULL,
    SHandphone,varchar(20),YES,,NULL,
    SSex,enum('M','F'),YES,,NULL,
    SDOB,date,YES,,NULL,
    SEmail,varchar(50),YES,,NULL,

    Student_Courses Structure
    Field,Type,Null,Key,Default,Extra
    StudentID,varchar(15),,PRI,,
    CourseLecturerID,int(10) unsigned,,PRI,0,
    CoordinatorID,int(10) unsigned,,PRI,0,

    Stud_Assignment Structure
    Field,Type,Null,Key,Default,Extra
    AssignID,int(11) unsigned,,PRI,NULL,auto_increment
    StudentID,int(10) unsigned,,PRI,0,
    Submit_Date,date,YES,,NULL,
    Assign_Filepath,varchar(100),YES,,NULL,
    Status,char(2),YES,,N,

    For every course there will be a unique CoordinatorID.
    For a those who have completed will be stored in the Stud_Assignment table.

    Now I want to get all those students names and ID who have NOT completed a particular assignment... Obviously those records that are not in Stud_Assignment, right?

    In Student_Courses table, for the CoordinatorID there are 66 records.
    In Stud_Assignment table, there are 2 records for the AssignId 89

    I wrote the following query
    SELECT A.StudentID, A.SFirstName
    FROM Student_Detail_Profile AS A, Student_Courses AS B
    LEFT OUTER JOIN Stud_Assignment AS C on (B.StudentID=C.StudentID)
    WHERE C.StudentID IS NULL and A.StudentID=B.StudentID and B.CoordinatorID=1082370452 AND C.AssignID=89


    but gets nothing..
    whats wrong with the query? Isn't the join correct?

  2. #2
    SitePoint Guru
    Join Date
    Jan 2004
    Location
    Uppsala, sverige
    Posts
    700
    Mentioned
    2 Post(s)
    Tagged
    1 Thread(s)
    Code:
    SELECT A.StudentID, A.SFirstName
    FROM Student_Detail_Profile AS A inner join Student_Courses AS B
    on A.StudentID=B.StudentID and B.CoordinatorID=1082370452 
    LEFT OUTER JOIN Stud_Assignment AS C 
    on B.StudentID=C.StudentID AND C.AssignID=89
    WHERE C.StudentID IS NULL


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •