Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
bendertez
Yak Posting Veteran
94 Posts |
 Posted - 2007-06-20 : 11:37:36
|
| HiI'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__Status101 Johns Course1 01/04/2007 Completed101 Johns Course2 01/04/2007 Completed101 Johns Course3 01/04/2007 Live101 Johns Course4 01/04/2007 Completed101 Johns Course5 01/04/2007 CompletedI 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. Thanks |
|
|
nr
SQLTeam MVY
12543 Posts |
Posted - 2007-06-20 : 11:43:52
|
| select distinct id, Namefrom tblwhere id not in (select distinct id from tbl where Status = 'Live')==========================================Cursors are useful if you don't know sql.DTS can be used in a similar way.Beer is not cold and it isn't fizzy. |
 |
|
|
|
|
|
SELECT Help....!!!