CODEWITHSUNDEEP
sqlsql-serverdatabase
seeker
seeker

Reputation: 3333

MSSQL: find records that has each value of column

Let's say I have some the following table schema: year|val1|val2 I want to get all the val1 and val2 columns that has all values of year column. I suspect I need grouping here, but I can't imagine how. Let's say we have set of years from query: SELECT DISTINCT YEAR FROM table. Let's say it returned 2000,2001. So If I have to rows as 2000|1|2 and 2001|1|2 then the query should return single row 1|2

Upvotes: 0

Views: 209

Answers (2)

Gulli Meel
Gulli Meel

Reputation: 891

Not sure about your question. But I think you need all the values of val1 and val2 which are there for all the valid values of years.If that's the case then you have to do inner join with the table something like this

select @yearcnt = count(distinct year) from table

or select val1,count(distint year) as totalyear from table group by val1 having count(distinct year) = @yearcnt

Upvotes: 0

Madhivanan
Madhivanan

Reputation: 13700

Something like this may work

select
    value1
from 
    table as t1 inner join
    (select distinct year from table) as t2
    on t1.year=t2.year
group by
    val1 
having count(distinct t1.year)=(count(distinct t2.year) )

Upvotes: 1

Related Questions