asked on

help with mysql query

Hi,

I have two tables
table one has three column
Student Name Studentcourse StudentID Date
xyz java 123 2009-10-15 15:10:00
abc c 456 2009-10-17 09:00:00
uvw sql 789 2009-10-18 16:00:00
jkg sql 000 2009-10-15 18:10:00

table two
StudentID grade
123 A
456 B
789 c

I need help with the query where we can get student who have taken sql on 15 of oct but where not graded that is not found table two.

I have come up with this query

select au.StudentID
from tabletwou ,tableone au
where au.Studentcourse='sql' and au.date
between '2009-10-15 00:00:00' AND '2009-10-15 23:59:59'and u.StudentID=auStudentID;

but I am getting wrong result...

TY

Cornelia Yoder

select au.StudentID
from tabletwo u, tableone au
where au.Studentcourse='sql' and au.date
between '2009-10-15 00:00:00' AND '2009-10-15 23:59:59'and u.StudentID=au.StudentID;

AdamRobinson

You aren't checking, in your query, on the grade. You'd want to check also where grade = ''

You may also want to considering joining the tables on student ID.

Guy Hengel [angelIII / a3]

you might try this:

select au.StudentID
    from tableone au 
    left join tabletwou s
       on u.StudentID = s.auStudentID
    where au.Studentcourse='sql' 
     and au.date >= '2009-10-15' 
     and au.date <  '2009-10-16'
     and s.auStudentID is null
     ;

Open in new window

Cornelia Yoder

I think you've both missed the typo where

from tabletwou, tableone au

should be

from tabletwo u, tableone au

AdamRobinson

I assumed that's not in the actual query and was a typo in re-typing it here, as the query wouldn't even run were he to have actually done that.

And, even with the typo, he's not checking on "grade" which he has to do to return the result he stated in the logic on the OP.

mkarthik415

ASKER

is there any way that query can be written for the above scenario using 'not in' I think that could be faster .

I do not want to check for the grade. I want to see which student taking course in 'sql' in not present in table two.

TY

Guy Hengel [angelIII / a3]

not in shall be "slower" than the left join approach.
alternatively, a not exists could be written, still with the same performance as the left join, usually

mkarthik415

ASKER

How to change the above query using "not in"

Guy Hengel [angelIII / a3]

this would do:

select au.StudentID
    from tableone au 
    where au.Studentcourse='sql' 
     and au.date >= '2009-10-15' 
     and au.date <  '2009-10-16'
     and au.StudentID  not in (select  s.auStudentID from tabletwou s where s.auStudentID is not null )
     ;

Open in new window

ASKER CERTIFIED SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

mkarthik415

ASKER