• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 291
  • 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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
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

Featured Post

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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