Solved

SQL 2005 query, filter out results if 1 column condition is met, and the 2nd is met(only if the 1st one was met)

Posted on 2009-07-08
6
280 Views
Last Modified: 2012-05-07
Sounds confusing...

I have an item location table where I am trying to filter out any part that is BOTH inactive and has a stock level of ZERO.

Example:
TABLE ITEM

Columns
ITEMID ACTIVE STOCK
1               1          15
2               0           0
3               0           15

I want to include in the query any parts that are inactive, however, have stock levels.  I want to filter out any ITEMID that is both inactive and has a stock level of 0.  Any itemID that has stock, I want to see.
Example results I'd like to see...
ITEMID ACTIVE STOCK
1               1          15
3               0           15

Any way to accomplish this through a query?
0
Comment
Question by:aerick911
6 Comments
 
LVL 75

Assisted Solution

by:Aneesh Retnakaran
Aneesh Retnakaran earned 50 total points
ID: 24804647
SELECT * FROM ITEM where Active =1
UNION ALL
SELECT * FROM Item WHERE Active = 0 AND Stock > 0
0
 
LVL 17

Expert Comment

by:pssandhu
ID: 24804651
Select *
From Item
Where Active = 0 and Stock = 0
0
 
LVL 17

Assisted Solution

by:pssandhu
pssandhu earned 100 total points
ID: 24804698
Err... sorry didn't read close enough, this will do as well:
Select * from Item Where (Active  = 0 and Stock >0) or Active = 1
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 
LVL 59

Expert Comment

by:Kevin Cross
ID: 24804736
How about a simple OR.
SELECT * 

FROM ITEM

WHERE Active = 1 -- All active regardless of stock level will show

   OR Stock > 0 -- All positive stock items will show regardless if active

Open in new window

0
 
LVL 59

Accepted Solution

by:
Kevin Cross earned 350 total points
ID: 24804752
If you want to see negative inventory also (i.e. you literally only want to filter inactive 0 stock, but inactive with negative stock is valid) then change like this:
SELECT * 

FROM ITEM

WHERE Active = 1

   OR Stock <> 0;

Open in new window

0
 

Author Closing Comment

by:aerick911
ID: 31601153
This was the best solution, seemed more efficient than the union query which would also work.
0

Featured Post

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the file…

707 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

16 Experts available now in Live!

Get 1:1 Help Now