Finding an occurrence that does not exist

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 2008 Forums

Transact-SQL (2008)

Finding an occurrence that does not exist

Author

Topic

andrew a
Starting Member

6 Posts

Posted - 2012-05-15 : 06:27:44

Hi everyone,
In the table you can create below, each set of order number records should contain one record with the OrderNoIndicator field set to 1. i.e. OrderNo 7032357. Is there a way of finding(without using the count function) sets of records that don’t contain an OrderNoIndicator set to 1 i.e. OrderNo 7088650 all the OrderNoIndicator fields are set to 2

Thanks in advance.
[SQL]
use LEIS
If OBJECT_ID ('dbo.temp') IS NOT NULL
DROP TABLE dbo.temp
GO
CREATE TABLE dbo.temp
( OrderNo bigint not null,
OrderNoindicator tinyint not null
);

INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
VALUES (7032357, 1 )
INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
VALUES (7032357, 2 )
INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
VALUES (7032357, 2 )
INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
VALUES (7034826, 1 )
INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
VALUES (7034826, 2 )
INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
VALUES (7088650, 2 )
INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
VALUES (7088650, 2 )
INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
VALUES (7088650, 2 )
INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
VALUES (7088650, 2 )
INSERT INTO dbo.temp (OrderNo, OrderNoindicator)
VALUES (7088650, 2 )

SELECT * FROM dbo.temp
[/SQL]

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-15 : 07:03:38

May be this?

SELECT DISTINCT OrderNo FROM temp a
WHERE NOT EXISTS (SELECT * FROM temp b WHERE b.OrderNo = a.OrderNo AND b.OrderNoindicator = 1)

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources