title: Query M:N contains
body: <p>I am trying to filter a set of tables that includes an M:N junction table in Android Room (SQLite).</p>
  
  <p>An image can have many subjects.  I'd like to allow filtering by a subject, so that I get a row with complete image information (including all subjects).  So if an image had (National Park, Yosemite) filtering for either would result in one row with both keywords.  Unless I messed something up, a typical join will result in multiple rows such that matching <code>Yosemite</code> would get the right image, but you'd be lacking <code>National Park</code>.  I came up with this:</p>
  
  <pre><code>SELECT *,  
    (SELECT GROUP_CONCAT(name) 
      FROM meta_subject_junction
      JOIN subject 
        ON subject.id = meta_subject_junction.subjectId
        WHERE meta_subject_junction.metaId = meta.id) AS keywords, 
    (SELECT documentUri
      FROM image_parent
      WHERE meta.parentId = image_parent.id ) AS parentUri
  FROM meta
  </code></pre>
  
  <p>Now this gets me the complete rows, but I think at this point I'd need to:</p>
  
  <pre><code>WHERE keywords LIKE(%YOSEMITE%)
  </code></pre>
  
  <p>and I think the <code>LIKE</code> is less than ideal, not to mention an imprecise match.  Is there a better way to accomplish this?  Thanks, this is bending my novice SQL brain.</p>
  
  <h2>Further details</h2>
  
  <pre><code>meta
  +----+----------+--+
  | id |   name   |  |
  +----+----------+--+
  |  1 | yosemite |  |
  |  2 | bryce    |  |
  |  3 | flowers  |  |
  +----+----------+--+
  subject
  +----+---------------+--+
  | id |     name      |  |
  +----+---------------+--+
  |  1 | National Park |  |
  |  2 | Yosemite      |  |
  |  3 | Tulip         |  |
  +----+---------------+--+
  junction
  +--------+-----------+
  | metaId | subjectId |
  +--------+-----------+
  |      1 |         1 |
  |      1 |         2 |
  |      2 |         1 |
  |      3 |         3 |
  +--------+-----------+
  </code></pre>
  
  <p>Although I may have done something wrong, as far as I can tell Android Room doesn't like:</p>
  
  <pre><code>+----+-----------+---------------+
  | id |   name    |    subject    |
  +----+-----------+---------------+
  |  1 | yosemite  | National Park |
  |  1 | yosemite  | Yosemite      |
  +----+-----------+---------------+
  </code></pre>
  
  <p>so I'm trying to reduce the rows:</p>
  
  <pre><code>+----+-----------+-------------------------+
  | id |   name    |         subject         |
  +----+-----------+-------------------------+
  |  1 | yosemite  | National Park, Yosemite |
  +----+-----------+-------------------------+
  </code></pre>
  
  <p>which the above query does.  However, I also want to query for a subject.  So that <code>National Park</code> filter will yield:</p>
  
  <pre><code>+----+-----------+-------------------------+
  | id |   name    |         subject         |
  +----+-----------+-------------------------+
  |  1 | yosemite  | National Park, Yosemite |
  |  2 | bryce     | National Park           |
  +----+-----------+-------------------------+
  </code></pre>
  
  <p>I'd like to be more precise/efficient than LIKE with the already 'concat' subject.  Most of my attempts end up with no results in Room (multi-row) or reducing the subject to only the filter keyword.</p>
  
  <h2>Update</h2>
  
  <p>Here's a test I've been using to compare the actual SQL results from a query to what Android Room ends up with:</p>
  
  <p><a href="http://sqlfiddle.com/#!7/0ac11/10/0" rel="nofollow noreferrer">http://sqlfiddle.com/#!7/0ac11/10/0</a></p>
  
  <p>That join query is interpreted as four objects in Android Room, so I'm trying to reduce the rows, but retain the full subject results while filtering for any image containing the subject keyword.</p>