| 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! |
|
|
|
|
|
|
select distinct rows that does not meet criteria