left join help
Posted: Sun Jun 13, 2004 10:45 pm
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_Profile 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 1082370452 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
i'm supposed to get 64 records but gets nothing..
whats wrong with the query? Isn't the join correct?
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_Profile 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 1082370452 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
i'm supposed to get 64 records but gets nothing..
whats wrong with the query? Isn't the join correct?