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

Filed under:
|

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

Related posts about sql

Related posts about sql-server-2005