title: Selecting items according to tags table with synonymous rows
body: <p>I have three tables: articles, tags and articles_tags(junction).</p>
  
  <p>inside <code>tags</code> I have 3 (relevant) columns:
  <code>id</code>, <code>name</code>, and <code>alias</code>.
  The <code>alias</code> column contains the ID of an earlier <em>synonymous</em> tag, if exists (<code>NULL</code> otherwise). <strong>There are no loops or chains: all synonymous tags contain the same alias.</strong> E.g.:</p>
  
  <h2><code>tags</code></h2>
  
  <pre><code>id    |    name    |    alias
  ------------------------------------
  ------------------------------------
  8     |   pencil   |    NULL
  ------------------------------------
  ------------------------------------
  3072  |   pencils  |      8
  ------------------------------------
  ------------------------------------
  3073  | blue pencil|      8
  ------------------------------------
  </code></pre>
  
  <p><em>(The tags <strong>MUST</strong> have this feature because they are user-typed.)</em></p>
  
  <p>Now, let's say I want to perform a search for all articles containing the tags <code>pencils</code> or <code>cats</code>. I would like them to include <em>ALL</em> other synonymous tags (<em>pencil</em>, <em>blue pencil</em>, <em>tomcat</em>, <em>kitten</em> etc.).</p>
  
  <p>The query I came up with is as following:</p>
  
  <pre><code>SELECT * FROM `articles` WHERE id IN
      (SELECT article_id FROM `articles_tags` WHERE id IN
           (SELECT id FROM `tags` WHERE COALESCE(alias, id) IN
              (SELECT id FROM `tags` WHERE name IN
                  ("pencils", "cats")
          )
      )
  )
  </code></pre>
  
  <p>I believe it is working, but I think there might a better way then using 4 subqueries with <em>IN</em>s (performance is key, because this will be a common search to perform).</p>
  
  <p>I would very much appreciate a guidance towards a better solution.</p>
  
  <p>Thanks.</p>
  
  <h2>Bonus Question</h2>
  
  <p>What if I need to find articles that contain no other tags than the ones searched for?</p>
  
  <p>That is, Selecting all articles that have only the tags "cats" and "dogs" will return articles whose only tags are either ("cats"), ("dogs"), or ("cats", "dogs").</p>
  
  <p><em>EDIT:</em> table structures:</p>
  
  <pre><code>id    |    name    |    alias
  ------------------------------------
  ...
  ------------------------------------
  8     |   pencil   |    NULL
  ------------------------------------
  ...
  ------------------------------------
  3072  |   pencils  |      8 (pencil)
  ------------------------------------
  ------------------------------------
  3073  | blue pencil|      8 (pencil)
  ------------------------------------
  ------------------------------------
  6088  |    cats    |     NULL
  ------------------------------------
  ------------------------------------
  7098  |    dogs    |     NULL
  ------------------------------------
  ------------------------------------
  7099  |  kittens   |     6088 (cats)
  ------------------------------------
  ------------------------------------
  7102  |  chiwawa   |     7098 (dogs)
  ------------------------------------
  </code></pre>
  
  <h2>articles</h2>
  
  <pre><code>id    |        title       |    content
  ----------------------------------------
  ...
  ----------------------------------------
  1     |   I love writing   |    ...
  ----------------------------------------
  ...
  ----------------------------------------
  42    |Tips for pet owners |    ...
  ----------------------------------------
  ----------------------------------------
  108   |  Drawing my dog    |    ...
  ----------------------------------------
  </code></pre>
  
  <h2>articles_tags</h2>
  
  <pre><code>id    |      article_id      |    tag_id
  ------------------------------------
  ...
  ------------------------------------
  19    |   1(I love writing)  |    3072 (pencils)
  ------------------------------------
  ------------------------------------
  21    |   1(I love writing)  |    3010 (poetry)
  ------------------------------------
  ------------------------------------
  22    |   1(I love writing)  |    123  (books)
  ------------------------------------
  ------------------------------------
  34    | 42(Tips for pet ...) |    6088 (cats)
  ------------------------------------
  ------------------------------------
  35    | 42(Tips for pet ...) |    7098 (dogs)
  ------------------------------------
  ...
  ------------------------------------
  78    | 108(Drawing my dog) |     7098 (dogs)
  ------------------------------------
  ------------------------------------
  78    | 108(Drawing my dog) |     8    (pencil)
  ------------------------------------
  </code></pre>
  
  <p><strong>desired output:</strong></p>
  
  <p>Searching for <code>chiwawa</code>, <code>cats</code> should yield "Tips for pet owners", "Drawing my dog".
  Searching for <code>pencil</code>, <code>dogs</code> should yield "I love writing", "Drawing my dog".</p>
  
  <p>And for the second (bonus) part:
  Searching for <code>chiwawa</code>, <code>kittens</code> should yield <strong>ONLY</strong> "Tips for pet owners", and <strong>NOT</strong> "Drawing my dog" because it also has a <code>pencils</code> tag.</p>
  
  <p><em>sorry for the amount of text.</em></p>