Query 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 2005 Forums

Transact-SQL (2005)

Query Help

Author

Topic

vijayakumar_svk
Yak Posting Veteran

50 Posts

Posted - 2008-12-17 : 23:50:53

I have a table of data

ID GroupID
-----------
1 1
1 2
1 3
2 1
2 2
4 1
4 1
4 1
5 1
5 1
5 2
5 3

From this above data I want to get the IDs which exist in all groups (1,2,3).

Expected result

ID
1
5

Thanks in advance
Vijay

==============================
Work smarter not harder take control of your life be a super achiever

vijayakumar_svk
Yak Posting Veteran

50 Posts

Posted - 2008-12-17 : 23:54:55

I have this solution..
select id from x where id1=1
intersect
select id from x where id1=2
intersect
select id from x where id1=3

I am looking if any alternate solution.

==============================
Work smarter not harder take control of your life be a super achiever

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-17 : 23:55:24

[code]SELECT ID
FROM YourTable
WHERE GroupID IN (1,2,3)
GROUP BY ID
HAVING COUNT(DISTINCT GroupID)=3[/code]

bklr
Master Smack Fu Yak Hacker

1693 Posts

Posted - 2008-12-18 : 00:03:54

select id from tabk
group by id HAVING COUNT(DISTINCT GroupID)= 3

not required where condition u have check the count in having clause

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 00:05:39

quote:
Originally posted by bklr

select id from tabk
group by id HAVING COUNT(DISTINCT GroupID)= 3

not required where condition u have check the count in having clause

what happens when you have other groups also present?

vijayakumar_svk
Yak Posting Veteran

50 Posts

Posted - 2008-12-18 : 00:30:35

Thanks visakh16... I tried the below one but yours looks fine.

select id,count(*) from
(select distinct id,id1 from x) as a
where id1 in (1,2,3)
group by id having count(*)=3

==============================
Work smarter not harder take control of your life be a super achiever

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2008-12-18 : 01:07:36

welcome

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources