Solved

exclude results from a query

Posted on 2013-06-03
8
442 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
 
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
3 Use Cases for Connected Systems

Our Dev teams are like yours. They’re continually cranking out code for new features/bugs fixes, testing, deploying, testing some more, responding to production monitoring events and more. It’s complex. So, we thought you’d like to see what’s working for us.

 
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
setup wamp server for first time 2 41
Mysql sync between 3-4 mysql db 4 23
Optimizing a query 3 33
sql_mode 1 16
Occasionally there is a need to clean table columns, especially if you have inherited legacy data. There are obviously many ways to accomplish that, including elaborate UPDATE queries with anywhere from one to numerous REPLACE functions (even within…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
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.
I designed this idea while studying technology in the classroom.  This is a semester long project.  Students are asked to take photographs on a specific topic which they find meaningful, it can be a place or situation such as travel or homelessness.…

932 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

10 Experts available now in Live!

Get 1:1 Help Now