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 the subset of values in the y
column, for that x
, matches exactly the values 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.
SELECT
table1.x
FROM
table1
INNER JOIN
table2
ON table1.y = table2.z
GROUP BY
table1.x
HAVING
COUNT(*) = (SELECT COUNT(*) FROM table2 AS lookup)
AND COUNT(*) = (SELECT COUNT(*) FROM table1 AS lookup WHERE x = table1.x)
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With