Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 293
  • Last Modified:

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

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
aerick911
Asked:
aerick911
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
SELECT * FROM ITEM where Active =1
UNION ALL
SELECT * FROM Item WHERE Active = 0 AND Stock > 0
0
 
pssandhuCommented:
Select *
From Item
Where Active = 0 and Stock = 0
0
 
pssandhuCommented:
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
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

 
Kevin CrossChief Technology OfficerCommented:
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
 
Kevin CrossChief Technology OfficerCommented:
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
 
aerick911Author Commented:
This was the best solution, seemed more efficient than the union query which would also work.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Get your problem seen by more experts

Be seen. Boost your question’s priority for more expert views and faster solutions

Tackle projects and never again get stuck behind a technical roadblock.
Join Now