SELECT WHERE COUNT = 1

Table LESSON has fields LessonDate, MemberId (Among others but only these two are relevant)

In English: Give me a list of the dates on which students that have only ever taken 1 class, took that class.

I have tried a number of things. Here's my latest attempt:

SELECT LessonDate 
FROM LESSON 
WHERE (SELECT COUNT(MemberId) GROUP BY (MemberId)) = 1

Just keeps returning SQL errors. Obviously, the syntax and logic are off.

Solution

The query may seem a little counter-intuitive at first. You need to group by MemberId to get the students who only took one class:

select max(l.LessonDate) as LessonDate
from Lesson l
group by l.MemberId
having count(*) = 1;

Because there is only one class for the student, max(l.LessonDate) is that date. Hence this does what you want: it gets all the dates for members who only took one class.

Your line of thinking is also pretty close. Here is the query that I think you were looking for:

SELECT LessonDate 
FROM LESSON 
WHERE MemberId in (SELECT l2.MemberId
                   FROM Lesson l2
                   GROUP BY l2.MemberId
                   HAVING COUNT(*) = 1
                  );

This approach is more generalizable, if you want to get the dates for members that have 2 or 3 rows.