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

[Matik]

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

[Ross Presser]

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)

[Mateusz Marzec]

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!