I'm trying to construct a SELECT statement that will return people that have NO live courses running at the moment. Please see example table below:
id Name Course StartDate Status 101 JohnS Course1 01/04/2007 Completed 101 JohnS Course2 01/04/2007 Completed 101 JohnS Course3 01/04/2007 Live 101 JohnS Course4 01/04/2007 Completed 101 JohnS Course5 01/04/2007 Completed
I need to find people who have live courses, which is simple enough (WHERE Status = 'Live'). But I also need another query to find people who have no live courses at all. If I do the obvious (WHERE status <> 'Live') it will record John Smith not being live as he has four completed courses where the status is NOT live.
From the above example, how can I get John Smith NOT appearing on a select statement when I'm looking for people who are not live at all (as he has one course that is live)?
There are several ways to approach this—self-joins, NOT EXISTS subqueries, and so on. But perhaps the simplest approach involves a GROUP BY:
select Name from Courses group by Name having sum( case when Status = 'Live' then 1 else 0 end ) = 0
The GROUP BY ensures that we get one result row for everybody in the table. (This is harder to achieve with other approaches.)
The solution involves simply counting the number of Live courses, and keeping only those Names where this count is 0.
This was first published in July 2007