Comparing two sets? (challenge!)
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.
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.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?
--
Steinar Overbeck Cook