StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>I assume you want one form of query that can be used to answer all three questions, not a different kind of query for each question.</p> <p>These solutions take advantage of <code>COUNT()</code> not counting <code>NULL</code>s. When the <code>OUTER JOIN</code> does not match a row in <code>t2</code>, it results in <code>NULL</code> for all columns from <code>t2</code>. </p> <p><strong>get ID 10, if I try to find (A,B,C)</strong></p> <p>ID 10 has three distinct term values, and we're searching for all three.</p> <pre><code>SELECT t1.ID FROM TableA t1 LEFT OUTER JOIN TableA t2 ON (t1.ID = t2.ID AND t1.term = t2.term AND t2.term IN ('A', 'B', 'C')) GROUP BY t1.ID HAVING COUNT(t1.term) = COUNT(t2.term); </code></pre> <p><strong>get NOTHING if I try to find (A,B)</strong></p> <p>Both ID 10 and ID 20 have three distinct term values, but our search is only for two. The counts are 3 = 2 for both IDs, so neither have equal counts.</p> <pre><code>SELECT t1.ID FROM TableA t1 LEFT OUTER JOIN TableA t2 ON (t1.ID = t2.ID AND t1.term = t2.term AND t2.term IN ('A', 'B')) GROUP BY t1.ID HAVING COUNT(t1.term) = COUNT(t2.term); </code></pre> <p><strong>get ID 20, if I try to find NOT in (C,D)</strong></p> <p>ID 20 has three distinct term values, and all three of them are NOT 'C' or 'D'. So the counts are equal.</p> <pre><code>SELECT t1.ID FROM TableA t1 LEFT OUTER JOIN TableA t2 ON (t1.ID = t2.ID AND t1.term = t2.term AND t2.term NOT IN ('C', 'D')) GROUP BY t1.ID HAVING COUNT(t1.term) = COUNT(t2.term); </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