StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>This is basically the same question as <a href="https://stackoverflow.com/questions/648308/filtering-from-join-table/">Filtering from join-table</a></p> <p>I'll adapt my answer from that question.</p> <p><strong>JOIN solution:</strong></p> <pre><code>SELECT m.* FROM messages m JOIN message_subject_rel ms1 ON (m.id = ms1.message_id) JOIN subjects s1 ON (ms1.subject_id = s1.id AND s1.subject = 'Math') JOIN message_subject_rel ms2 ON (m.id = ms1.message_id) JOIN subjects s2 ON (ms2.subject_id = s2.id AND s2.subject = 'Science'); </code></pre> <p><strong>GROUP BY solution:</strong></p> <p>Note that you need to list all m.* columns in the <code>GROUP BY</code> clause, unless you use MySQL.</p> <pre><code>SELECT m.* FROM messages m JOIN message_subject_rel ms ON (m.id = ms.message_id) JOIN subjects s ON (ms.subject_id = s.id) WHERE s.subject IN ('Math', 'Science')) GROUP BY m.id, ... HAVING COUNT(*) = 2; </code></pre> <p><strong>Subquery solution:</strong></p> <pre><code>SELECT m.* FROM messages m WHERE m.id = ANY (SELECT message_id FROM message_subject_rel ms JOIN subjects s ON (ms.subject_id = s.id) WHERE s.subject = 'Math') AND m.id = ANY (SELECT message_id FROM message_subject_rel ms JOIN subjects s ON (ms.subject_id = s.id) WHERE s.subject = 'Science'); </code></pre> <p><strong>Modified GROUP BY solution:</strong></p> <p>Simplifies GROUP BY clause by isolating search in a subquery.</p> <pre><code>SELECT m.* FROM messages m WHERE m.id IN ( SELECT ms.message_id FROM message_subject_rel ms JOIN subjects s ON (ms.subject_id = s.id) WHERE s.subject IN ('Math', 'Science')) GROUP BY ms.message_id HAVING COUNT(*) = 2 ); </code></pre> <p>PS: There's no reason your <code>message_subject_rel</code> table needs an <code>ID</code> column.</p>

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