starter's query. need help. - 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. need help.

Author

Topic

Sundeep
Starting Member

1 Post

Posted - 2004-04-16 : 06:23:11

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.

ditch
Master Smack Fu Yak Hacker

1466 Posts

Posted - 2004-04-16 : 07:28:47

This should do it:

--**** Create test data

CREATE TABLE MyTable01 (Col1 VARCHAR(1), Col2 INT)

INSERT INTO MyTable01 VALUES('a',1)
INSERT INTO MyTable01 VALUES('a',2)
INSERT INTO MyTable01 VALUES('b',1)
INSERT INTO MyTable01 VALUES('b',2)
INSERT INTO MyTable01 VALUES('b',3)
INSERT INTO MyTable01 VALUES('c',1)
INSERT INTO MyTable01 VALUES('c',2)
INSERT INTO MyTable01 VALUES('c',4)

--**** Select the data

SELECT DISTINCT a.Col2
FROM MyTable01 a
WHERE a.Col2 in (SELECT b.Col2 FROM (SELECT c.Col2, COUNT(DISTINCT c.Col1) as CT
FROM MyTable01 c
GROUP BY c.Col2) b
WHERE b.CT = (SELECT COUNT(DISTINCT Col1) FROM MyTable01))

Duane.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources