StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

PO
text
Body
copied!<p>So you need to find the Media for which there exists a language.code of 'eng' but no entries in the join table for the same media_id but a different language_id.</p> <pre><code>SELECT * FROM media m1 JOIN audio_languages_media alm1 ON alm1.media_id = m1.id JOIN languages l1 ON alm1.language_id = l1.id WHERE NOT EXISTS( SELECT 1 FROM audio_languages_media alm2 WHERE alm1.language_id != alm2.language_id AND alm1.media_id = alm2.media_id ) AND l1.code = 'eng'; </code></pre> <p>Let us know if this is the right db query so we can help with the AREL.</p> <p>Edit: Query for when you want to find a media that is in at least 'eng' and 'fra'</p> <pre><code>SELECT * FROM media m1 WHERE( SELECT count(*) FROM audio_languages_media alm2 JOIN languages l2 ON alm2.language_id = l2.id WHERE l2.code in ('eng','fra') AND alm2.media_id = m1.id ) > 1; </code></pre> <p>Edit: Add @chinshr's query</p> <p>If you want media that has only exactly 'eng' and 'fra'</p> <pre><code>SELECT * FROM media m1 WHERE( SELECT count(*) FROM audio_languages_media alm2 JOIN languages l2 ON alm2.language_id = l2.id WHERE l2.code IN ('eng','fra') AND alm2.media_id = m1.id AND ( SELECT count(*) FROM audio_languages_media alm2 WHERE alm2.media_id = media.id ) = 2 ) = 2; </code></pre> <p>This query can be tweaked for more or less languages by adding/removing from the IN array, and adjusting the count at the end to be equal to the number of elements in the IN array.</p> <p>For this to work reliably, you must have a unique index on audio_languages_media(media_id, language_id);</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