[SOLVED] left join help

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

left join help

Post 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?
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post by psmshankar »

I'm using MySQL 3.23
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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...
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post by psmshankar »

I don't understand..

is there any other way to write the query?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

as I read it, you're asking it to return rows where B.StudentID is null... which probably is zero rows.
Last edited by feyd on Sun Jun 13, 2004 11:23 pm, edited 1 time in total.
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post 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..
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

try it with:

LEFT OUTER JOIN Stud_Assignment AS C on (B.StudentID=A.StudentID)
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post by psmshankar »

no..still not
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post 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
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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) ?
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post 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..
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post 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)
User avatar
feyd
Neighborhood Spidermoddy
Posts: 31559
Joined: Mon Mar 29, 2004 3:24 pm
Location: Bothell, Washington, USA

Post by feyd »

can you put an export of the three tables up somewhere so I/we may better help?
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post 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)
psmshankar
Forum Commoner
Posts: 96
Joined: Tue Aug 06, 2002 4:25 am
Location: India

Post 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..
Post Reply