Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
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
Medium Priority
?
288 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 200 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 400 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
Does Your Cloud Backup Use Blockchain Technology?

Blockchain technology has already revolutionized finance thanks to Bitcoin. Now it's disrupting other areas, including the realm of data protection. Learn how blockchain is now being used to authenticate backup files and keep them safe from hackers.

 
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 1400 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…
Want to learn how to record your desktop screen without having to use an outside camera. Click on this video and learn how to use the cool google extension called "Screencastify"! Step 1: Open a new google tab Step 2: Go to the left hand upper corn…

664 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