Find matches from one column

[TheCandyman]

I'm trying to write a SQL statement that will look at one column and only show those that have both values. For example, in looking for users that have both 'aaaa' AND 'bbbb' would be User1.

Column1  Column2
User1     aaaa
User2     bbbb
User1     bbbb
User1     cccc
User3     aaaa

Currently I'm getting all those that have either 'aaaa' or 'bbbb' but not both, and simply switching the OR to a AND results in no results.

SELECT DISTINCT col1
FROM table
WHERE (col2='aaaa' OR col2='bbb');

 

[gmmastros]

Select Col1
From   Table
Group By Col1
Having Count(Case When Col2 = 'aaaa' Then 1 End) > 0
       And Count(Case When Col2 = 'bbbb' Then 1 End) > 0

-George
Microsoft SQL Server MVP
My Blogs
SQLCop
"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[TheCandyman]

Can't say i have ever used 'When', i figured it would require a nested SQL statement