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
285 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
[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
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
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 60

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 60

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

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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

Title # Comments Views Activity
SQL query to select row with MAX date 7 67
Problem with MySQL query - graph 3 46
Better way to filter date  - Query 5 45
Tracking Problematic Page Splits 1 50
If you find yourself in this situation “I have used SELECT DISTINCT but I’m getting duplicates” then I'm sorry to say you are using the wrong SQL technique as it only does one thing which is: produces whole rows that are unique. If the results you a…
This article describes how to use the timestamp of existing data in a database to allow Tableau to calculate the prior work day instead of relying on case statements or if statements to calculate the days of the week.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

751 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