starter's query :( - SQL Server Forums

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 2000 Forums

Transact-SQL (2000)

starter's query :(

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2004-04-26 : 11:34:29

Sundeep writes "Hi, i am trying to make a query but it should be in a optimized way. Need help.

Situation is, I have one table with 2 columns, col 1, col2 and the records in the table are like
Col1, Col2
a,1
a,2
b,1
b,2
b,3
c,1
c,2
c,4

As you can see the distinct values in column Col1 are a,b,c.
Now, I want to get all the records in col2, where col2 value has record containg all the distinct values of col1.
i.e. for the above mentioned sample, it should return me

Col2
1
2

only. As, for col2 = 1, i have col1 = a, b, c and also for
col2 = 2, i have col1 = a,b,c.

Thanks in advance."

drymchaser
Aged Yak Warrior

552 Posts

Posted - 2004-04-26 : 11:59:36

I am sure this won't fulfill all your requirements but:

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources