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 4 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)?
Any advice would be much appreciated.