CODEWITHSUNDEEP
sqlsql-server
user3439907
user3439907

Reputation: 11

SQL Server 2008, display only one value from col1 which has multiple values associated in col2 of same table

I have this table

**Col1      Col2**
A            1          
A            2
A            3
A            4
B            1
B            2
B            3
B            4
c            1
c            2

Now with this, I need to find Values from col1 which is related to four values of col2. e.g.if 'A' is related to the four values in col2 'A' should be displayed and same with 'B' but not 'c', don't have to display values from col2 but if you can that's a plus. And I don't have any query for this.please help

Upvotes: 0

Views: 56

Answers (2)

mohan111
mohan111

Reputation: 8865

;with CTE AS
(
select distinct col1,COUNT(col2)as Col2,ROW_NUMBER()OVER(ORDER BY COL1) AS RN  from @coltable
GROUP BY col1
)
select COL1,Col2 from CTE WHERE RN <> 3

Upvotes: 0

Szymon
Szymon

Reputation: 43023

You can do it this way, provided that you want to get the values from col1 that there's a corresponding value in col2 for all values of col2:

select col1
from coltable
group by col1
having count(distinct col2) = (select count(distinct col2) from coltable)

SQL Fiddle demo

Upvotes: 1

Related Questions