Select records by comparing subsets
Posted
by
devnull
on Stack Overflow
See other posts from Stack Overflow
or by devnull
Published on 2012-07-11T13:14:13Z
Indexed on
2012/07/11
15:15 UTC
Read the original article
Hit count: 183
sql
|sql-server-2005
Given two tables (the rows in each table are distinct):
1) x | y z 2) x | y z
------- --- ------- ---
1 | a a 1 | a a
1 | b b 1 | b b
2 | a 1 | c
2 | b 2 | a
2 | c 2 | b
2 | c
Is there a way to select the values in the x
column of the first table for which all the values in the y
column (for that x
) are found in the z
column of the second table?
In case 1)
, expected result is 1
. If c
is added to the second table then the expected result is 2
.
In case 2)
, expected result is no record
since neither of the subsets in the first table matches the subset in the second table. If c
is added to the second table then the expected result is 1, 2
.
I've tried using except
and intersect
to compare subsets of first table with the second table, which works fine, but it takes too long on the intersect
part and I can't figure out why (the first table has about 10.000 records and the second has around 10).
EDIT: I've updated the question to provide an extra scenario.
© Stack Overflow or respective owner