[SOLVED] left join help
Moderator: General Moderators
-
psmshankar
- Forum Commoner
- Posts: 96
- Joined: Tue Aug 06, 2002 4:25 am
- Location: India
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_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?
-
psmshankar
- Forum Commoner
- Posts: 96
- Joined: Tue Aug 06, 2002 4:25 am
- Location: India
-
psmshankar
- Forum Commoner
- Posts: 96
- Joined: Tue Aug 06, 2002 4:25 am
- Location: India
-
psmshankar
- Forum Commoner
- Posts: 96
- Joined: Tue Aug 06, 2002 4:25 am
- Location: India
I mentioned C.StudentID is NULL, in the query.
All I need is the Student's list that are not in Stud_Assignment with the given criteria..
Normally it will be like
SELECT table1.field1
FROM table1
LEFT JOIN table2 on (table1.key=table2.key)
WHERE table2.field2 IS NULL
i have added few more critireia in the where condition as i need to filter out..
All I need is the Student's list that are not in Stud_Assignment with the given criteria..
Normally it will be like
SELECT table1.field1
FROM table1
LEFT JOIN table2 on (table1.key=table2.key)
WHERE table2.field2 IS NULL
i have added few more critireia in the where condition as i need to filter out..
-
psmshankar
- Forum Commoner
- Posts: 96
- Joined: Tue Aug 06, 2002 4:25 am
- Location: India
-
psmshankar
- Forum Commoner
- Posts: 96
- Joined: Tue Aug 06, 2002 4:25 am
- Location: India
i tried like this with two tables.., removed the AssignID=89
SELECT Distinct A.StudentID
FROM Student_Courses AS A
LEFT OUTER JOIN Stud_Assignment AS B on (A.StudentID=B.StudentID)
WHERE A.CoordinatorID=1082370452 AND
B.StudentID IS NULL
eventually this works and gives me 64 records..
but this is wrong as i need to compare the AssignID so that those who have not submitted that assignment, will be listed out...

SELECT Distinct A.StudentID
FROM Student_Courses AS A
LEFT OUTER JOIN Stud_Assignment AS B on (A.StudentID=B.StudentID)
WHERE A.CoordinatorID=1082370452 AND
B.StudentID IS NULL
eventually this works and gives me 64 records..
but this is wrong as i need to compare the AssignID so that those who have not submitted that assignment, will be listed out...
-
psmshankar
- Forum Commoner
- Posts: 96
- Joined: Tue Aug 06, 2002 4:25 am
- Location: India
-
psmshankar
- Forum Commoner
- Posts: 96
- Joined: Tue Aug 06, 2002 4:25 am
- Location: India
-
psmshankar
- Forum Commoner
- Posts: 96
- Joined: Tue Aug 06, 2002 4:25 am
- Location: India
I've copied the three tables and renamed to
Shankar_Detail_Profile, Shankar_Courses and Shankar_Assignment.
I have some dummy data as well and exported to here
http://pesona.mmu.edu.my/~sankara/
You can find the Shankar.sql
still i don't get if i add AssignID=1 in that query(u gotta change the table names accordingly)...
let me know whether u could..
Shankar_Detail_Profile, Shankar_Courses and Shankar_Assignment.
I have some dummy data as well and exported to here
http://pesona.mmu.edu.my/~sankara/
You can find the Shankar.sql
still i don't get if i add AssignID=1 in that query(u gotta change the table names accordingly)...
let me know whether u could..