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.
| Author |
Topic |
|
query12
Starting Member
6 Posts |
 Posted - 2012-10-11 : 05:56:21
|
| 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 |
|
|
bandi
Master Smack Fu Yak Hacker
2242 Posts |
Posted - 2012-10-11 : 06:36:52
|
| 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 tGROUP BY id2having COUNT(id2) = (SELECT COUNT(*) from #a)--Chandu |
 |
|
|
|
|
|
|
|
tricky query