Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

SQL to return if a single row(can be>1) and certain criteria

Status
Not open for further replies.

andrewRA

Programmer
Sep 27, 2006
1
US
Not sure how to approach this as I keep going in circles. I want to return ID's that soley have keyword='ISV' and no other keyword. I tried some count(*)=1 and 'GROUP BY' but it does not seem to work.

Example Table

id keyword
== =======
A ISV
A Reseller
A Foo
B ISV
C ISV
D Foo
D ISV

So the desired result would just id of B,C while A,D are ignored because they have additional keywords.

Thanks for any direction.
 
Code:
select id 
  from t 
  group by id
  having sum(case when keyword = 'ISV' then 0 else 1 end) = 0
 
The Following query is Generic Query, you can do it for any keyword,

Select Distinct ID, KeyWord
From
(
Select
ID,
KeyWord,
(Select Count(Distinct KeyWord) From Ta Sub Where Sub.Id = Main.Id) KeyWordCount
From
Ta Main
) as Data
Where
KeyWord = 'ISV'
And KeyWordCount = 1
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top