Thread: simplifying SQL

I have this query which essentially returns records that exist in one
table (studentclass) and not another (effortandattainment) to check when
teachers have entered their student grades.

select
distinct(studentclass.studentclassid),classes.subject,studentclass.studentid as
classstudent,classes.teacher,students.studentid,students.firstname,students.surname 
from studentclass left join effortandattainment
on(effortandattainment.classid=studentclass.classid and
effortandattainment.studentid=studentclass.studentid),students,classes

where students.studentid=studentclass.studentid
and studentclass.classid=classes.classid
and studentclass.classid not in (
select studentclass.classid
from studentclass inner join effortandattainment
on(effortandattainment.classid=studentclass.classid and
effortandattainment.studentid=studentclass.studentid)
)

I was wondering whether a simpler method exists to do the same thing or
is this OK.
Regards
Garry

garry saddington wrote:
> I have this query which essentially returns records that exist in one
> table (studentclass) and not another (effortandattainment) to check when
> teachers have entered their student grades.
>
> select

> from studentclass left join effortandattainment

> and studentclass.classid not in (

> I was wondering whether a simpler method exists to do the same thing or
> is this OK.

The formatting on the query isn't great, but I can't quite see what the
"left join" is doing with the "not in" too. I'd probably just have the
left join and check that "effortandattainment.classid IS NULL".

--
   Richard Huxton
   Archonet Ltd