| Author |
 Topic  |
|
|
query12
Starting Member
6 Posts |
 Posted - 10/11/2012 : 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 |
Edited by - query12 on 10/11/2012 05:56:57
|
|
|
bandi
Flowing Fount of Yak Knowledge
India
1390 Posts |
Posted - 10/11/2012 : 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 t
GROUP BY id2
having COUNT(id2) = (SELECT COUNT(*) from #a)
--
Chandu |
 |
|
| |
Topic |
|
|
|
tricky query
