Excluding all records where a value exists

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)

Excluding all records where a value exists

Author

Topic

Joeray1983
Starting Member

2 Posts

Posted - 2011-05-10 : 11:48:05

I have table that is set up as such:

LN LSC
1 35
1 1
1 2
1 2
1 3
1 3
1 2
2 35
2 2
2 2
2 2
3 1
3 1
3 1
3 1
4 4
4 4
4 4
4 4

What I'm looking to do is exclude all records in LN where a value of 35 exists for LSC.

So if 35 exists in LN's 1 and 2, I want to exclude all records for 1 and 2.

My final table should look like as follows:

LN LSC
3 1
3 1
3 1
3 1
4 4
4 4
4 4
4 4

-- Q:What do you do with an elephant with three balls?

A: Walk him and pitch to the rhino

latch
Yak Posting Veteran

62 Posts

Posted - 2011-05-10 : 12:31:41

select LN,LSC from table where LN not in (select distinct LN from table where LSC=35)

or

select LN,LSC from table where LN not in (select distinct LN from table where LSC in (35))

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2011-05-10 : 14:15:07

Or may be even this?


select ln,lsc from YourTable t1
where not exists
( select * from YourTable t2 where t2.ln = t1.ln and t2.lsc=35 );

Joeray1983
Starting Member

2 Posts

Posted - 2011-05-11 : 10:29:19

Awesome. Thanks for the help!

-- Q:What do you do with an elephant with three balls?

A: Walk him and pitch to the rhino

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources