Please start any new threads on our new site at We've got lots of great SQL Server experts to answer whatever question you can come up with.

Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums.

SQL Server Forums
Profile | Active Topics | Members | Search | Forum FAQ
Save Password
Forgot your Password?

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

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?

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

Flowing Fount of Yak Knowledge

2242 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?

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

Go to Top of Page
  Previous Topic Topic Next 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.02 seconds. Powered By: Snitz Forums 2000