How to ignore group of records ? - SQL Server Forums

Home | Weblogs | Forums

Site Sponsored By: SQLDSC - SQL Server Desired State Configuration

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)

How to ignore group of records ?

Author

Topic

Thennarasu
Starting Member

2 Posts

Posted - 2009-05-19 : 04:00:00

Hi, I have a table(consumption) with fields: unit & operation. The key for this table is unit & operation.

Sample data will look like,
200, Action1
200, Action2
200, Action3
201, Action4
201, Action2
202, Action1
202, Action2
202, Action4

I have to list all the units and operation for those units which doesn't have any Action3 operation.

The data i am looking out is,
201, Action4
201, Action2
202, Action1
202, Action2
202, Action4

I have been spending hell a lot of hours on finding the solution. Can any one help me in building the SQL query to filter these records.

No stored procedures please.

Thanks
Thenn

khtan
In (Som, Ni, Yak)

17689 Posts

Posted - 2009-05-19 : 04:17:18

[code]
DECLARE @consumption TABLE
(
unit int,
operation varchar(10)
)

INSERT INTO @consumption
SELECT 200, 'Action1' UNION ALL
SELECT 200, 'Action2' UNION ALL
SELECT 200, 'Action3' UNION ALL
SELECT 201, 'Action4' UNION ALL
SELECT 201, 'Action2' UNION ALL
SELECT 202, 'Action1' UNION ALL
SELECT 202, 'Action2' UNION ALL
SELECT 202, 'Action4'

SELECT *
FROM @consumption c
WHERE c.unit NOT IN
(
SELECT unit
FROM @consumption
WHERE operation = 'Action3'
)
/*
unit operation
----------- ----------
201 Action4
201 Action2
202 Action1
202 Action2
202 Action4

(5 row(s) affected)
*/
[/code]

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

pmotewar
Yak Posting Veteran

62 Posts

Posted - 2009-05-19 : 04:18:38

select * from consumption
where unit not in ( select unit from consumption where operation='Action3')

Note : Note Tested

Pankaj

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-19 : 04:24:37

[code]
select unit, operation from consumption c
where not exists (select * from consumption c1 where c1.unit=c.unit and c1.operation='Action3')
[/code]
That should work

greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-19 : 04:25:20

too late...

No, you're never too old to Yak'n'Roll if you're too young to die.

Thennarasu
Starting Member

2 Posts

Posted - 2009-05-19 : 06:27:38

Really appreciate your help and time. Thanks a lot.

Thanks
Thenn

quote:
Originally posted by webfred

select unit, operation from consumption c
where not exists (select * from consumption c1 where c1.unit=c.unit and c1.operation='Action3')

That should work

greetings
Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-05-20 : 05:23:20

welcome

No, you're never too old to Yak'n'Roll if you're too young to die.

Nageswar9
Aged Yak Warrior

600 Posts

Posted - 2009-05-20 : 05:36:22

Hi try this once,

select t.unit,t.operation from @consumption t
inner join ( select unit from @consumption where operation = 'action3' )t1 on t.unit <> t1.unit

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2009-05-20 : 05:41:24

[code]
SELECT c.*
FROM @consumption c
LEFT JOIN @consumption cd on c.unit = cd.unit and cd.operation = 'Action3'
WHERE cd.unit IS NULL
[/code]

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources