title: Select translation for given string
body: <p>I am beginner in SQL and trying to design a query. I use my database for storing translations of different strings (which are used in UIs of different products).</p>
  
  <p>My database contains 4 tables:</p>
  
  <ul>
  <li><strong>String</strong> (id, name, lang_id): </li>
  </ul>
  
  <p>"lang_id" is FK references "id" from "Language" table</p>
  
  <ul>
  <li><p><strong>Copy</strong> (id, name)</p></li>
  <li><p><strong>Language</strong> (id, name)</p></li>
  <li><p><strong>Translation</strong> (id, string_1, string_2): </p></li>
  </ul>
  
  <p>"string_1" and "string_2" are FKs that reference "id" from "String" table.</p>
  
  <p>All my tables are using InnoDB engine, except <strong>Copy</strong> table which uses MyISAM engine and contains fulltext index on "name" column. I have triggers to ensure that IDs of strings in "String" and "Copy" tables match.</p>
  
  <p><strong>Use case:</strong></p>
  
  <p>User wants to translate new strings (which are not in DB yet) from english to German. User wants to find a reference translations for a string "Login" in order to reuse it and keep consistency.</p>
  
  <p>User submits data (for example using a form on the website):</p>
  
  <ul>
  <li>Input language: English</li>
  <li>Output language: German</li>
  <li>String: Login</li>
  </ul>
  
  <p>What user wants to get:</p>
  
  <p>All strings from DB, which contain word "Login" and their corresponding German translation.</p>
  
  <p>What I have so far:
  ID of English language is 1,
  ID of German language is 2</p>
  
  <pre><code>SELECT * FROM String s 
  JOIN (SELECT id FROM Copy 
        WHERE MATCH (name) AGAINST ('Login')) r 
  ON s.id = r.id WHERE s.language = 1
  </code></pre>
  
  <p>That returns all strings in English, containing "Login".</p>
  
  <p>I cannot figure what to do next, I think I should somehow join this result with "Translation" table.</p>
  
  <p>Could someone guide me in the right direction?</p>
  
  <p>P.S. I am using MySQL 5.5 (that's why I have this "Copy" table) and I understand that it might be better to use search engine for such tasks, but I want to accomplish it without one.</p>
  
  <p>Thank you in advance.</p>
  
  <p><strong>EDIT</strong></p>
  
  <p>For example I have:</p>
  
  <pre><code>       String                         Translation               Language
  |id| name     | lang_id |     |id| string_1 | string_2 |      |id| name |
  -------------------------     --------------------------      -----------
  | 1| Good     |    1    |     |1 |    1     |     2    |      | 1| En   |
  | 2| Gut      |    2    |     |2 |    4     |     3    |      | 2| De   |          
  | 3| Good day |    1    |
  | 4| Guten Tag|    2    |     
  </code></pre>
  
  <p>I search for "Good", input lang "En", output lang "De", want to get result:</p>
  
  <pre><code>| Good     | Gut       |
  | Good day | Guten Tag |
  </code></pre>
  
  <p><strong>EDIT</strong></p>
  
  <p><a href="http://sqlfiddle.com/#!2/0aaa4f/1/0" rel="nofollow">sqlfiddle</a></p>