Query dependent on three fields

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Query dependent on three fields

Author

Topic

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-10-13 : 15:05:49

I am trying to do a select query like below...

SELECT     INVENTORY_ITEM_TAB.ITEM_NO, INVENTORY_ITEM_TAB.DESCR, INVENTORY_ITEM_TAB.STATUS_FLG, INVENTORY_ITEM_TAB.PRICINGUOM, 
                      INVENTORY_ITEM_TAB.PURCHUOM, INVENTORY_ITEM_TAB.ITEM_CATEGORY, INVENTORY_ITEM_TAB.ICINTERNALNOTES, INVENTORY_WHS.QTY_ON_HAND, 
                      INVENTORY_WHS.QTY_ON_ORDER, INVENTORY_WHS.QTY_ALLOCATED
FROM         INVENTORY_ITEM_TAB INNER JOIN
                      INVENTORY_WHS ON INVENTORY_ITEM_TAB.ITEM_NO = INVENTORY_WHS.ICWHSPCODE
WHERE     (INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS') AND QTY_ALLOCATED, QTY_ON_HAND, Qty_On_ORDER <> 0
ORDER BY INVENTORY_ITEM_TAB.DESCR

But I don't want to select records if all of these fields have a 0 in them - QTY_ALLOCATED and QTY_ON_HAND and QTY_ON_ORDER.

I'm not sure how to do this type of query. If any of those fields doesn't have a 0 then I would want to return it. I just don't want them if all three of those fields have 0.

Hope that makes sense.
Stacy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-13 : 15:17:02

WHERE INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS' AND QTY_ALLOCATED <> 0 AND QTY_ON_HAND <> 0 AND Qty_On_ORDER <> 0

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

StacyOW
Yak Posting Veteran

93 Posts

Posted - 2014-10-13 : 15:40:06

quote:
Originally posted by tkizer

WHERE INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS' AND QTY_ALLOCATED <> 0 AND QTY_ON_HAND <> 0 AND Qty_On_ORDER <> 0

Thanks for your reply Tara. I tried that combination also but then I get no records and there are records in there that have a 0 in two of the three columns. I do want it to return the records if one or two of the columns has a 0 I just don't want the records if all three columns have a 0.

Thanks,
Stacy

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-10-13 : 15:54:14

Sorry I didn't read your post carefully. I just looked at your code sample and saw the syntax issue.

WHERE INVENTORY_ITEM_TAB.ITEM_CATEGORY = 'SS' AND (QTY_ALLOCATED <> 0 OR QTY_ON_HAND <> 0 OR Qty_On_ORDER <> 0)

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources