title: MySQL Language translation query
body: <p><a href="https://i.stack.imgur.com/JAJqd.png" rel="nofollow noreferrer"><img src="https://i.stack.imgur.com/JAJqd.png" alt="**I Used below approach for database**"></a>
  I am using multi language supported database. But i don't know how to query translated text from database based on Language.</p>
  
  <p>Here is my DDL for tables. Please ask me if you have any question.</p>
  
  <p>Category Table</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `catalog_category` (
    `id` int(10) unsigned NOT NULL,
    `name_translation_id` int(10) unsigned NOT NULL,
    `description_translation_id` int(10) unsigned NOT NULL,
  
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  </code></pre>
  
  <p>Language Table</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `languages` (
    `id` int(10) unsigned NOT NULL,
    `language_name` varchar(255) COLLATE utf8_unicode_ci NOT NULL,
    `language_code` varchar(2) COLLATE utf8_unicode_ci NOT NULL,
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  </code></pre>
  
  <p>Language Translation table</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `language_translation` (
    `id` int(10) unsigned NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  </code></pre>
  
  <p>Language Translation Entry Table</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `language_translation_entry` (
    `id` int(10) unsigned NOT NULL,
    `translation_id` int(10) unsigned NOT NULL,
    `language_id` int(10) unsigned NOT NULL,
    `translation_text` text COLLATE utf8_unicode_ci NOT NULL
  ) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci;
  </code></pre>
  
  <p>Currently i am using this query to get results but it is giving me from only name. I want description also in results.</p>
  
  <pre><code>SELECT lte.translation_text  as name FROM catalog_category AS cc
  LEFT JOIN language_translation lt ON lt.id = cc.name_translation_id
  LEFT JOIN language_translation_entry AS lte ON lte.translation_id = lt.id
  LEFT JOIN languages AS l on l.id = lte.language_id
  WHERE l.language_code = "en"
  </code></pre>
  
  <p>I need to get translated text from Translation Entry Table for category name and description based on language code in a single query.</p>