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: 257
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