help with mysql query

mkarthik415
mkarthik415 used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
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;
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]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Ensure you’re charging the right price for your IT

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden using our free interactive tool and use it to determine the right price for your IT services. Start calculating Now!

Commented:
I think you've both missed the typo where

from tabletwou, tableone au

should be

from tabletwo u, tableone au
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.

Author

Commented:
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]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Author

Commented:
How to change the above query using "not in"
Guy Hengel [angelIII / a3]Billing Engineer
Most Valuable Expert 2014
Top Expert 2009

Commented:
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

Billing Engineer
Most Valuable Expert 2014
Top Expert 2009
Commented:
with NOT ExISTS
select au.StudentID
    from tableone au 
    where au.Studentcourse='sql' 
     and au.date >= '2009-10-15' 
     and au.date <  '2009-10-16'
     and NOT EXISTS(select  s.auStudentID from tabletwou s where s.auStudentID = au.StudentID   )
     ;

Open in new window

Author

Commented:
TY

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial