Answer

Use HAVING with CASE to count specific instances

I'm trying to construct a SELECT statement that will return people that have NO live courses running at the moment.

Rudy Limeback
By
Published: 20 Jul 2007

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.

Dig Deeper on Oracle development languages