Re: Comparing two sets? (challenge!)
Our correspondent writes:-
>
> I've just struggled with a problem where I needed to compare two sets of
> data i.e. every item in A should also exists in B.
These set-membership puzzles do seem tricky to do in "pure" SQL, don't
they? Things like "list all the people who have taken the same combination
of classes as George".
> I have solved it, but I think my solution is not perfect, so if anyone
> would like a challenge, here goes:
>
> table T1: table T2:
>
> userid | code master | code
> --------------- --------------
> roy | 1 M1 | 1
> bill | 1 M1 | 2
> roy | 2 M2 | 1
> peter | 1
> foo | 2
>
> I wan't to retrieve the userids which have *all* the codes specified for a
> given master.
I take it you mean "and no others"?
That is, you want the people that have (1,2) but not the people that
have (1,2,4)?
It seems to me that what one really needs to be able to is to calculate
a unique "signature" for each set of codes. Then you can just related
each master to its signature, and each user to his signature (your users
all sound male, although 'foo' is a bit indeterminate). Then it is a
simple matter to get all users with the same signature as 'M1'.
One way to assign such a signature would be to create assign a 'weight'
to each code as follows:-
code weight
---- ------
1 10
2 20
3 40
4 80
5 160
6 320
7 640
Then the signature for any set of codes is simply the sum of the
corresponding weights.
(I multiplied the 'natural' weights by 10, since in the example the only
codes referred to are '1' and '2', and it would be extremely confusing
to have the weights equal the codes throughout the example when this
wouldn't work in general.)
Here's a little demo of how it might work.
create temp table T1 ( userid char(8), code smallint ) ;
insert into T1 values ( 'roy', 1 ) ;
insert into T1 values ( 'bill', 1 ) ;
insert into T1 values ( 'roy', 2 ) ;
insert into T1 values ( 'peter', 1 ) ;
insert into T1 values ( 'foo' , 2 ) ;
create temp table T2 ( master char(2), code smallint ) ;
insert into T2 values ( 'M1', 1 ) ;
insert into T2 values ( 'M1', 2 ) ;
insert into T2 values ( 'M2', 1 ) ;
create temp table aux1 ( code smallint, weight integer ) ;
insert into aux1 values ( 1, 10 ) ;
insert into aux1 values ( 2, 20 ) ;
insert into aux1 values ( 3, 40 ) ;
insert into aux1 values ( 4, 80 ) ;
select master, sum(weight) signature from T2, aux1
where T2.code = aux1.code
group by 1
into temp T2a ; { T2a contains: master signature
------ ---------
M2 10
M1 30 }
select userid, sum(weight) signature
from T1, aux1
where T1.code = aux1.code
group by 1
into temp T1a ; { T1a contains: userid signature
------ ---------
roy 30
peter 10
bill 10
foo 20 }
select userid
from T1a, T2a
where T1a.signature = T2a.signature
and T2a.master = 'M1' ;
which yields:- userid
------
roy as required.
On the other hand I may be mis-understanding your problem, because
when I run your SELECT below I get "no rows found" - I suppose I may
have mis-typed it.
Paul
PS I love SQL puzzles, so anyone please post more!
> I.e. SELECT code FROM T2 WHERE master = 'M1' will result in the set
> (1,2). Now I want to retrieve alle the userids from table T1 which
> have *all* the codes from table T2.
> For T2.master = 'M1' I want the result 'roy' from T1.
>
> My solution is akward,and assumes that duplicate combinations of
> 'userid' and 'code' are not allowed.
>
> SELECT userid FROM T1
> GROUP BY 1
> HAVING (SELECT COUNT(*)
> FROM T1
> WHERE code IN (SELECT code
> FROM T2
> WHERE T2.master='M1'
> )
> ) = (SELECT COUNT(*) FROM T2 WHERE T2.master='M1')>
> Does anyone have a more elegant solution?