Excluding rows with two conditions

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

Excluding rows with two conditions

Author

Topic

tech2
Yak Posting Veteran

51 Posts

Posted - 2011-08-07 : 20:29:31

Why is the sql statement below excluding 'ProductID's' 680, 706, 709, & 710? The only row that should be excluded is ID 535.

USE AdventureWorks
SELECT * FROM Production.Product
WHERE ReorderPoint <> 600 AND Size IS NULL

ProductID Name ProductNumber ReorderPoint Size
533 Seat Tube ST-9828 375 NULL
534 Top Tube TO-2301 375 NULL
535 Tension Pulley TP-0923 600 NULL
679 Rear Derailleur Cage RC-0291 375 NULL
680 HL Road Frame - Black, 58 FR-R92B-58 375 58
706 HL Road Frame - Red, 58 FR-R92R-58 375 58
707 Sport-100 Helmet, Red HL-U509-R 3 NULL
708 Sport-100 Helmet, Black HL-U509 3 NULL
709 Mountain Bike Socks, M SO-B909-M 3 M
710 Mountain Bike Socks, L SO-B909-L 3 L
711 Sport-100 Helmet, Blue HL-U509-B 3 NULL

Thanks

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2011-08-07 : 20:40:08

Your query


USE AdventureWorks
SELECT * 
FROM   Production.Product
WHERE  ReorderPoint <> 600 AND Size IS NULL
[code]

The Data
[code]
ProductID Name                       ProductNumber ReorderPoint Size
533       Seat Tube                  ST-9828       375          NULL
534       Top Tube                   TO-2301       375          NULL
535       Tension Pulley             TP-0923       600          NULL
679       Rear Derailleur Cage       RC-0291       375          NULL
680       HL Road Frame - Black, 58  FR-R92B-58    375          58
706       HL Road Frame - Red, 58    FR-R92R-58    375          58
707       Sport-100 Helmet, Red      HL-U509-R       3          NULL
708       Sport-100 Helmet, Black    HL-U509         3          NULL
709       Mountain Bike Socks, M     SO-B909-M       3          M
710       Mountain Bike Socks, L     SO-B909-L       3          L
711       Sport-100 Helmet, Blue     HL-U509-B       3          NULL

The result should be those with BLUE AND green

KH
[spoiler]Time is always against us[/spoiler]

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 04:58:58

as you see the reason is because condition Size IS NULL does not hold good for 680, 706, 709, & 710 so they also get excluded

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

tech2
Yak Posting Veteran

51 Posts

Posted - 2011-08-08 : 10:17:07

quote:
Originally posted by visakh16

as you see the reason is because condition Size IS NULL does not hold good for 680, 706, 709, & 710 so they also get excluded

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Thanks for replying visakh16 & khtan but I still don't have a statement that works. Do either of you have any ideas or suggestions. This should be a simple sql statement. The sql instruction is to exclude all rows that meet both conditions, which is ID 535. This is not happening.

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2011-08-08 : 13:02:14

[code]
USE AdventureWorks
SELECT *
FROM Production.Product
WHERE NOT (ReorderPoint = 600 AND Size IS NULL)
[/code]

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources