StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POSQL comparing sets, part II: How to join sets of sets
text
Body
copied!<p>This <a href="https://stackoverflow.com/questions/3661766/tsql-comparing-two-sets/3661993#3661993">question</a> reminded me of a couple related problems with whole-set comparison. Given:</p> <ol> <li>a <code>collection</code> of sets, and </li> <li>a <code>probe</code> set</li> </ol> <p>Three questions:</p> <ol> <li>How do you find all sets in <code>collection</code> that match <code>probe</code>, element for element?</li> <li>How do you find all sets in <code>collection</code> that match a collection of <code>probe</code>s, without the use of explicit looping constructs? How do you join sets of sets?</li> <li>Is this relational division? If not, what is it?</li> </ol> <p>I have a decent solution to question 1 (see below). </p> <p>I don't have a decent relational solution to question 2. Any takers?</p> <p>Test data:</p> <pre><code>IF OBJECT_ID('tempdb..#elements') IS NOT NULL DROP TABLE #elements IF OBJECT_ID('tempdb..#sets') IS NOT NULL DROP TABLE #sets CREATE TABLE #sets (set_no INT, PRIMARY KEY (set_no)) CREATE TABLE #elements (set_no INT, elem CHAR(1), PRIMARY KEY (set_no, elem)) INSERT #elements VALUES (1, 'A') INSERT #elements VALUES (1, 'B') INSERT #elements VALUES (1, 'C') INSERT #elements VALUES (1, 'D') INSERT #elements VALUES (1, 'E') INSERT #elements VALUES (1, 'F') INSERT #elements VALUES (2, 'A') INSERT #elements VALUES (2, 'B') INSERT #elements VALUES (2, 'C') INSERT #elements VALUES (3, 'D') INSERT #elements VALUES (3, 'E') INSERT #elements VALUES (3, 'F') INSERT #elements VALUES (4, 'B') INSERT #elements VALUES (4, 'C') INSERT #elements VALUES (4, 'F') INSERT #elements VALUES (5, 'F') INSERT #sets SELECT DISTINCT set_no FROM #elements </code></pre> <p>Setup and solution for question 1, set lookup:</p> <pre><code>IF OBJECT_ID('tempdb..#probe') IS NOT NULL DROP TABLE #probe CREATE TABLE #probe (elem CHAR(1) PRIMARY KEY (elem)) INSERT #probe VALUES ('B') INSERT #probe VALUES ('C') INSERT #probe VALUES ('F') -- I think this works.....upvotes for anyone who can demonstrate otherwise SELECT set_no FROM #sets s WHERE NOT EXISTS ( SELECT * FROM #elements i WHERE i.set_no = s.set_no AND NOT EXISTS ( SELECT * FROM #probe p WHERE p.elem = i.elem)) AND NOT EXISTS ( SELECT * FROM #probe p WHERE NOT EXISTS ( SELECT * FROM #elements i WHERE i.set_no = s.set_no AND i.elem = p.elem)) </code></pre> <p>Setup for question 2, no solution:</p> <pre><code>IF OBJECT_ID('tempdb..#multi_probe') IS NOT NULL DROP TABLE #multi_probe CREATE TABLE #multi_probe (probe_no INT, elem CHAR(1) PRIMARY KEY (probe_no, elem)) INSERT #multi_probe VALUES (1, 'B') INSERT #multi_probe VALUES (1, 'C') INSERT #multi_probe VALUES (1, 'F') INSERT #multi_probe VALUES (2, 'C') INSERT #multi_probe VALUES (2, 'F') INSERT #multi_probe VALUES (3, 'A') INSERT #multi_probe VALUES (3, 'B') INSERT #multi_probe VALUES (3, 'C') -- some magic here..... -- result set: -- probe_no | set_no ------------|-------- -- 1 | 4 -- 3 | 2 </code></pre>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload