SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 tricky query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

query12
Starting Member

6 Posts

Posted - 10/11/2012 :  05:56:21  Show Profile  Reply with Quote
I have relation A(id1) and relation B(id2, id1). How can I get all id2's from B that have entries for all the values of id1 in A?
Eg, A = {1,2,3}, B = {(a,1), (a,2), (a,2), (b,1), (b,2), (b,3)} and I would get as a result {b} (, where a & b are some numbers, wrote them as letters for clarity).
As you may have noticed, counting does not solve this matter, as (a,2) for instance can appear multiple times. This is an oversimplified rep of my relations, so no worries for primary keys.

Any ideas?
Thanks

Edited by - query12 on 10/11/2012 05:56:57

bandi
Flowing Fount of Yak Knowledge

India
2224 Posts

Posted - 10/11/2012 :  06:36:52  Show Profile  Reply with Quote

create table #A (id1 int)
INSERT INTO #A values (1),(2),(3)

create table #B (id2 char(1), id1 int)
insert into #B values('a',1), ('a',2), ('a',2), ('b',1), ('b',2), ('b',3)

--How can I get all id2's from B that have entries for all the values of id1 in A?


SELECT id2
FROM (SELECT distinct * from #B) as t
GROUP BY id2
having COUNT(id2) = (SELECT COUNT(*) from #a)

--
Chandu
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.14 seconds. Powered By: Snitz Forums 2000