Networks

Ask something
Question Locked

Simple SELECT query from SQL Server

0 helpful
2 replies

by dcleary

Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

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.

2 replies

by psomoorthi

You can use another table to level the status and use Inner Join to use to solve out your requirement.
Do as follows.
Create a table with fields Status, status level.
and insert data's as below.
Status Level
Joined, 1
started, 2
Live, 3
Completed, 4

I am given as example of status, it can be something which suits your requirement.
Now you can query like this
select a.id,a.Name,a.startdate,a.status from table1 as a inner join statustab as b on (a.status = b.status) where statuslevel<=3.
it gives you the perfect output.

Please test it as i have not tested it.

Thanks & regards

P.Somoorthi.
UltraTech Cement Limited.

This discussion is closed to new replies.