Solved

exclude results from a query

Posted on 2013-06-03
8
470 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

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

Suggested Solutions

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 …
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…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

737 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