469,364 Members | 2,332 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,364 developers. It's quick & easy.

Selecting ONLY records from one table having ALL data contained in other table (GROUP BY?)

Hello everyone,

Small and (I think) very simple quesiton;-) which makes me creazy.
Let's say I have two tables listed below:

T1
====
IDX
====
1
3
4

T2
===============
IDD fk_IDX
===============
A 1
A 2
A 4
B 1
B 3
B 4
C 4
D 1
D 2
D 3
D 4

I would like to select from table T2 all distinct records IDD which
have all of fk_IDX containded in T1.
The select statement should return in this case ONLY:
B and D
becasue:
B has 1,3,4
and
D has 1,2,3,4 so it has this combination 1,3,4 contained in the T1
also.

I've tried to do that with group by, with having, in and it never
works (I always became all records which one of them is in this T1
table).

Maybe some one from you did try something like that, and can give a
fast answer.

I will be very greatfull

Greatings

Mateusz
Jul 20 '05 #1
2 2180
On 17 Aug 2004 11:13:14 -0700, Matik wrote:
Hello everyone,

Small and (I think) very simple quesiton;-) which makes me creazy.
Let's say I have two tables listed below:
[snip tables]

I would like to select from table T2 all distinct records IDD which
have all of fk_IDX containded in T1.


I think what you want is this technique:

select IDD from T2
inner join T1 on T2.fk_IDX = T1.IDX
group by IDD
having count(T2.fk_IDX) = (select count(*) from T1)

You should always try to post DDL and sample inserts when you ask questions
here; it makes your problem much clearer and makes it easier for those who
wish to help -- we can just paste into query analyzer and begin.

Below is complete DDL, inserts, your solution and its results, as copied
from query analyzer:

use tempdb

create table T1 (IDX int primary key)
create table T2 (IDD char(1), fk_IDX int,
constraint pk_t2 primary key (IDD, fk_IDX))

insert into T1 (IDX)
select 1 union all select 3 union all select 4

insert into T2 (IDD, fk_IDX) VALUES ('A',1)
insert into T2 (IDD, fk_IDX) VALUES ('A',2)
insert into T2 (IDD, fk_IDX) VALUES ('A',4)
insert into T2 (IDD, fk_IDX) VALUES ('B',1)
insert into T2 (IDD, fk_IDX) VALUES ('B',3)
insert into T2 (IDD, fk_IDX) VALUES ('B',4)
insert into T2 (IDD, fk_IDX) VALUES ('C',4)
insert into T2 (IDD, fk_IDX) VALUES ('D',1)
insert into T2 (IDD, fk_IDX) VALUES ('D',2)
insert into T2 (IDD, fk_IDX) VALUES ('D',3)
insert into T2 (IDD, fk_IDX) VALUES ('D',4)

select IDD from T2
inner join T1 on T2.fk_IDX = T1.IDX
group by IDD
having count(T2.fk_IDX) = (select count(*) from T1)

IDD
----
B
D

(2 row(s) affected)
Jul 20 '05 #2

Thank you very much for reply, and for information about the rules.
This statement works perfectly.

Mateusz

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!
Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

6 posts views Thread by aaj | last post: by
11 posts views Thread by Ron L | last post: by
4 posts views Thread by Jeffrey Davis | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Marylou17 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.