database query, giving unexpected result

Question

I'm trying to keep track my students, and those that have not yet booked a lesson with me (I'm a music teacher as well as a CS student) but my attempt is actually giving me all those that HAVE already booked lessons, what is my mistake?

SELECT DISTINCT student.person_id FROM student, lesson 
WHERE lesson.student = student.person_id 
AND (select count(*) 
     FROM lesson 
     WHERE student = student.person_id 
     AND start_time > NOW()) = 0

Answer 1

Try:

SELECT DISTINCT s.person_id 
  FROM STUDENT s  
 WHERE NOT EXISTS(SELECT NULL 
                    FROM LESSONS l
                   WHERE l.student = s.person_id
                     AND l.start_time > NOW())

Answer 2

Robert,

Making reasonable assumptions about how you might be storing information, your query selects those students for whom your database lists no future scheduled lesson. It doesn't matter whether a student has a past lesson or not. I'm not sure what you mean by "not yet booked a lesson," because that English phrase is somewhat awkward. Is it different from "not/never booked a lesson"? The words "not yet" indicate not before the present time, suggesting that you keep track of both booked lessons that have already been booked and also booked lessons that are "not yet booked" (whatever that means). Without a crystal ball, I don't know how you keep track of lessons that students have booked, but just not yet.

In any case, if for a particular student your database lists no lessons that start in the future for that student, you will see that student in your results (regardless of any lessons you list for that student that start now or in the past).

There's also an odd thing about your query, and that's the fact that you are joining the tables "lesson" and "student" on lesson.student = student.person_id before restricting the results based on a count. It seems to me that if you want a list of students satisfying some condition, you'd only need "student" in the FROM clause of the outer query, as in rexem's query.

If you want to see those students for whom you have no record of any lesson (regardless of lessons' start times), rexem's query should work. DISTINCT is unnecessary if the table "student" lists each student only once. The advantage of using EXISTS is that with EXISTS, as opposed to COUNT, you don't suggest needing to count up the total number of future lessons for each student before comparing the count with zero. With EXISTS, you suggest no need to keep looking for a student's future lessons once any such lesson is found.