Solved

exclude results from a query

Posted on 2013-06-03
8
432 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 24

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
 
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
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
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

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Creating and Managing Databases with phpMyAdmin in cPanel.
Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

708 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

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now