Solved

exclude results from a query

Posted on 2013-06-03
8
454 Views
Last Modified: 2013-07-02
Here is a query:

select id, status, datedone from items join statuses on items.status_id = statuses.status_id where closed = 0

I need to exclude where status_id=14 and datedone is not null

What would be a good way to go about this?

Thanks!

Randy
0
Comment
Question by:rjohnsonjr
8 Comments
 
LVL 20

Expert Comment

by:dsacker
ID: 39216617
select id, status, datedone from items join statuses on items.status_id = statuses.status_id where closed = 0 and items.status_id != 14 and items.datedone is not null
0
 
LVL 20

Expert Comment

by:dsacker
ID: 39216625
ooops, if you are excluding where items.datedone is not null, then verse that one criteria (include where items.datedone is null)

select id, status, datedone from items join statuses on items.status_id = statuses.status_id where closed = 0 and items.status_id != 14 and items.datedone is null
0
 
LVL 25

Expert Comment

by:Tomas Helgi Johannsson
ID: 39216628
Hi!

Something like this

select id, status, datedone
from items i, statuses s
where i.status_id = s.status_id
and s.closed = 0
and i.status_id <> 14
and s.datedone is not null

Make sure that the s or i prefix is correct as I'm assuming that the columns belongs to the tables.

Regards,
    Tomas Helgi
0
Microsoft Certification Exam 74-409

Veeam® is happy to provide the Microsoft community with a study guide prepared by MVP and MCT, Orin Thomas. This guide will take you through each of the exam objectives, helping you to prepare for and pass the examination.

 
LVL 5

Expert Comment

by:DOSLover
ID: 39216631
select id, status, datedone
  from items
    join statuses on items.status_id = statuses.status_id
where closed = 0
     and  items.status_id <> 14
     and datedone is NOT NULL;
0
 
LVL 6

Author Comment

by:rjohnsonjr
ID: 39216644
I am not sure why I am having trouble with this today.  Will the query pull back entries with status_id=14 and datedone is null?

I guess I just need to test this..  

Monday Mental Block....
0
 
LVL 6

Author Comment

by:rjohnsonjr
ID: 39216668
I went ahead and created another table to take out the join to test this.

select * from randy where closed=0 and status_id <>14 and datedone is null

This does not work the way I want it to because it does not show the rows with status=14 and datedone is null

There will be lots of rows returned, but I do not want to return the ones that have a status=14 and a datedone that is filled in.
0
 
LVL 20

Accepted Solution

by:
dsacker earned 500 total points
ID: 39216720
Ah, so you only want to exclude those for status=14 and a value in datedone, but not exclude datedone if it's other than status=14. One query should do the trick:

select id, status, datedone
from items
join statuses on items.status_id = statuses.status_id
where closed = 0
and ((items.status_id = 14 and items.datedone is not null) or items.status_id <> 14)

Open in new window

0
 
LVL 50

Expert Comment

by:Steve Bink
ID: 39216935
SELECT id, status, datedone
FROM items i
    INNER JOIN statuses s ON i.status_id=s.status_id
WHERE
    closed=0
    AND NOT (i.status_id=14 AND i.datedone IS NOT NULL)

Open in new window

0

Featured Post

VMware Disaster Recovery and Data Protection

In this expert guide, you’ll learn about the components of a Modern Data Center. You will use cases for the value-added capabilities of Veeam®, including combining backup and replication for VMware disaster recovery and using replication for data center migration.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Popularity Can Be Measured Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for …
Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
Email security requires an ever evolving service that stays up to date with counter-evolving threats. The Email Laundry perform Research and Development to ensure their email security service evolves faster than cyber criminals. We apply our Threat…

832 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question