StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This can be accomplished with <a href="http://en.wikipedia.org/wiki/Relational_algebra#Division_.28.C3.B7.29" rel="nofollow">relational division</a>:</p> <pre><code>select r.order_id from ( select dividend.* from your_table_or_query as dividend -- assumes no duplicates in `dividend`; use `distinct` if there are any inner join divisor on dividend.value = divisor.value ) as r group by r.order_id having count(*) = (select count(*) from divisor); </code></pre> <p>result:</p> <pre><code>+----------+ | order_id | +----------+ | 1236 | | 1239 | +----------+ 2 rows in set (0.00 sec) </code></pre> <p>where your query is <code>your_table_or_query</code> and </p> <pre><code>select 260 as value from dual union select 264 as value from dual </code></pre> <p>is <code>divisor</code>.</p> <p>This will return the order ids 1236 and 1239; they can then be <code>join</code>ed to original query to get all the rows with those order ids if that's what you want.</p> <hr> <p>Full query along with insert statements:</p> <pre><code>create table divisor (value int); insert into divisor values (260), (264); create table your_table_or_query (value int, order_id int); insert into your_table_or_query values (260, 1234), (260, 1235), (260, 1236), (264, 1236), (260, 1237), (260, 1238), (260, 1239), (264, 1239), (264, 1240), (260, 1241); select y.* from ( select r.order_id from ( select dividend.* from your_table_or_query as dividend inner join divisor on dividend.value = divisor.value ) as r group by r.order_id having count(*) = (select count(*) from divisor) ) as quotient inner join your_table_or_query y on quotient.order_id = y.order_id; </code></pre> <p>Result:</p> <pre><code>+-------+----------+ | value | order_id | +-------+----------+ | 260 | 1236 | | 264 | 1236 | | 260 | 1239 | | 264 | 1239 | +-------+----------+ 4 rows in set (0.00 sec) </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