We help IT Professionals succeed at work.

Check out our new AWS podcast with Certified Expert, Phil Phillips! Listen to "How to Execute a Seamless AWS Migration" on EE or on your favorite podcast platform. Listen Now

x

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

Medium Priority
309 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?
Comment
Watch Question

AneeshDatabase Consultant
CERTIFIED EXPERT
Top Expert 2009
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Commented:
Select *
From Item
Where Active = 0 and Stock = 0
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview
Kevin CrossChief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011

Commented:
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

Chief Technology Officer
CERTIFIED EXPERT
Most Valuable Expert 2011
Commented:
Unlock this solution with a free trial preview.
(No credit card required)
Get Preview

Author

Commented:
This was the best solution, seemed more efficient than the union query which would also work.
Unlock the solution to this question.
Thanks for using Experts Exchange.

Please provide your email to receive a free trial preview!

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.