Page 1 of 2

left join help

Posted: Sun Jun 13, 2004 10:45 pm
by psmshankar
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?

Posted: Sun Jun 13, 2004 10:54 pm
by psmshankar
I'm using MySQL 3.23

Posted: Sun Jun 13, 2004 11:05 pm
by feyd
the logic in here:

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

seems a bit screwy...

Posted: Sun Jun 13, 2004 11:08 pm
by psmshankar
I don't understand..

is there any other way to write the query?

Posted: Sun Jun 13, 2004 11:14 pm
by feyd
as I read it, you're asking it to return rows where B.StudentID is null... which probably is zero rows.

Posted: Sun Jun 13, 2004 11:20 pm
by psmshankar
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..

Posted: Sun Jun 13, 2004 11:25 pm
by feyd
try it with:

LEFT OUTER JOIN Stud_Assignment AS C on (B.StudentID=A.StudentID)

Posted: Sun Jun 13, 2004 11:32 pm
by psmshankar
no..still not

Posted: Sun Jun 13, 2004 11:44 pm
by psmshankar
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... :o 8O

Posted: Sun Jun 13, 2004 11:51 pm
by feyd
is there a reason why Student_Detail_Profile.StudentID and Student_Courses.StudentID are both varchar(15), yet Stud_Assignment.StudentID is int(10) ?

Posted: Mon Jun 14, 2004 12:03 am
by feyd
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 = A.StudentID )
WHERE C.StudentID = 0 AND B.CoordinatorID =1082370452 AND C.AssignID = 89

appears to work..

Posted: Mon Jun 14, 2004 1:06 am
by psmshankar
the data type of StudentID shud also be varchar(15)..
thats human error..

i corrected that...

i tried your query, but it doesn't seem to fetch results for me...
infact it took a long time to show no records(54.01 secs)

Posted: Mon Jun 14, 2004 1:12 am
by feyd
can you put an export of the three tables up somewhere so I/we may better help?

Posted: Mon Jun 14, 2004 1:29 am
by psmshankar
Do you mean with data..
I can't as :
Student_Detail_Profile contains 9152 records
Student_Courses contains 69309

I can do for Stud_Assignment as it has jsut around 20 records...(its in development)

Posted: Mon Jun 14, 2004 2:01 am
by psmshankar
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..