Listing if it has one value but not other

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)

Listing if it has one value but not other

Author

Topic

torlok2002
Starting Member

11 Posts

Posted - 2009-04-22 : 15:52:53

I'm trying to query a table and return a result if the account has a cpt code like 93510%, but it to be dropped off the query if it also has a cpt code like 93543%. Any ideas on how to approach this? I'm still drawing blanks.

Here is my query so far to get the following output

select distinct account, cpt from table where (cpt like'93510%' or cpt like '93543%') and company='MAIN'

Account cpt
-----------------
213787 93510
213787 93543
213787 9351026
85482 9351026
85482 93543
190986 9351026
190986 93543
82573 9351026
82573 93543
213771 9351026
213771 93543
3269 9351026
3269 93543
300139 9351026
300139 93543
207055 93543
94009 9351026
94009 93543

nr
SQLTeam MVY

12543 Posts

Posted - 2009-04-22 : 15:58:41

select distinct account, cpt from table
where cpt like'93510%'
and company='MAIN'
and account in (select account fom table where
(cpt like'93510%' or cpt like '93543%')
and company='MAIN'
group by account having max(left(cpt,5)) = '93510'

==========================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2009-04-22 : 16:05:41

Here are a couple of ways to do it depending on the size of your data:

SELECT A.*
FROM
	(
		select distinct account, cpt 
		from table 
		where cpt like'93510%' and company='MAIN'
	) AS A
LEFT OUTER JOIN
	(
		select distinct account 
		from table 
		where cpt like'93543%' and company='MAIN'
	) AS B
	ON A.account = B.Account
WHERE 
	B.Account IS NULL
	
	
select distinct account, cpt 
from table 
where 
	cpt like'93510%' 
	and company='MAIN'
	AND account NOT IN (SELECT DISTINCT account FROM table WHERE cpt like'93543%' and company='MAIN')

torlok2002
Starting Member

11 Posts

Posted - 2009-04-23 : 16:38:29

Great, after a bit of massaging I was able to make it work! Thanks a ton. Never used NOT IN before.

Qualis
Posting Yak Master

145 Posts

Posted - 2009-04-23 : 16:43:14

Please do not EVER use Not In with a sub-select. It can have disastrous results on performance. Not In is appropriate for small lists like:


Select ColorName From MyColors
Where ColorName Not In ('Red', 'Green', 'Salmon')

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources