Solved

exclude results from a query

Posted on 2013-06-03
8
461 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
Webinar: Aligning, Automating, Winning

Join Dan Russo, Senior Manager of Operations Intelligence, for an in-depth discussion on how Dealertrack, leading provider of integrated digital solutions for the automotive industry, transformed their DevOps processes to increase collaboration and move with greater velocity.

 
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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Confronted with some SQL you don't know can be a daunting task. It can be even more daunting if that SQL carries some of the old secret codes used in the Ye Olde query syntax, such as: (+)     as used in Oracle;     *=     =*    as used in Sybase …
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 …
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

856 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