select distinct rows that does not meet criteria

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

SQL Server 2005 Forums

Transact-SQL (2005)

select distinct rows that does not meet criteria

Author

Topic

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-01-28 : 12:36:46

Hi

I have a ShoppingCart table that can contain multiple article rows, each row has a CartNumber column that is the same for all rows, like this....



ArticleID  Qty   CartNumber    Attention   DateAdded
1           1      111-22       1           2014-01-01
2           1      111-22       0           2014-01-01
1           1      222-33       0           2014-01-01
2           4      222-33       0           2014-01-01

I want to select a list of distinct CartNumber, but it should only list the ones that does not have any Attention = 1, in other words, the only CartNumber to be displayed from the above rows should be "222-333" since it only contains Attention = 0.

Can someone show me how?

sqlsaga
Yak Posting Veteran

93 Posts

Posted - 2014-01-28 : 16:17:38

[code]

DECLARe @Cart TABLE
(
ArticleID INT,
Qty INT,
CartNumber VARCHAR(10),
Attention BIT,
DateAdded DATE
)

INSERT INTO @Cart VALUES(1,1,'111-22', 1, '2014/01/01'), (2,1,'111-22', 0, '2014/01/01')
, (1,1,'222-33', 0, '2014/01/01'), (2,4,'222-33', 0, '2014/01/01')

SELECT Distinct CartNumber FROM @Cart a
WHERE NOT EXISTS (SELECT CartNumber FROM @Cart b WHERE a.CartNumber = b.CartNumber AND b.Attention = 1)
[/code]

Visit www.sqlsaga.com for more t-sql snippets and BI related how to's.

magmo
Aged Yak Warrior

558 Posts

Posted - 2014-01-29 : 03:13:50

Great, works perfect. Thanks!

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources