title: SQL OUTER JOIN with empty fields (null) where no result
body: <p>Hulloes SO!</p>
  
  <p>Okay, so my case is this: I have a db where I need to SELECT from 4 different tables and get results where my foregin keys in one table matches the ids in my other 3 tables.</p>
  
  <p>Structure:</p>
  
  <pre><code>CREATE TABLE IF NOT EXISTS `languages` (
    `id` int(11) NOT NULL,
    `lang_name` varchar(64) COLLATE utf8_bin NOT NULL
  ) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  
  CREATE TABLE IF NOT EXISTS `main_keys` (
    `id` int(11) NOT NULL,
    `project` int(11) NOT NULL,
    `key` text COLLATE utf8_bin NOT NULL,
    `comment` varchar(128) COLLATE utf8_bin NOT NULL
  ) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  
  CREATE TABLE IF NOT EXISTS `projects` (
    `id` int(11) NOT NULL,
    `project_name` varchar(64) COLLATE utf8_bin NOT NULL
  ) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  
  CREATE TABLE IF NOT EXISTS `translations` (
    `id` int(11) NOT NULL,
    `project` int(11) NOT NULL,
    `key` int(11) NOT NULL,
    `language` int(11) NOT NULL,
    `translation` text COLLATE utf8_bin NOT NULL,
    `lastedit` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP
  ) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 COLLATE=utf8_bin;
  </code></pre>
  
  <p>The <code>translations</code> table is the table with all the relations (<code>translations.project</code> => <code>project.id</code>, <code>translations.key</code> => <code>main_keys.id</code>, and <code>translations.language</code> => <code>language.id</code>).</p>
  
  <p><a href="http://assets.brianemilius.com/locale_relations.png" rel="nofollow noreferrer">Relational overview http://assets.brianemilius.com/locale_relations.png</a></p>
  
  <p>My current query looks like this</p>
  
  <pre><code>SELECT t.id, mk.key, t.translation, mk.comment, p.project_name, t.lastedit
      FROM translations t
      INNER JOIN languages l ON l.id = t.language
      INNER JOIN main_keys mk ON mk.id = t.key
      INNER JOIN projects p ON p.id = mk.project
      WHERE p.id = '1' AND l.id = '2'
  </code></pre>
  
  <p>I am selecting from a specific project(p.id) and a specific language(l.id) and I want a result, which shows something like this:</p>
  
  <pre><code>+----+--------------+-------------+---------------------------+-----------------------+---------------------+
  | id | key          | translation | comment                   | project_name          | lastedit            |
  +----+--------------+-------------+---------------------------+-----------------------+---------------------+
  | 2  | Hello World! | Hej verden! | Test key for dev purposes | Locale Administrator  | 2015-02-24 12:37:28 |
  +----+--------------+-------------+---------------------------+-----------------------+---------------------+
  |    | test key     |             | Test key for dev purposes | Locale Administrator  |                     |
  +----+--------------+-------------+---------------------------+-----------------------+---------------------+
  |    | test key 2   |             | Test key for dev purposes | Locale Administrator  |                     |
  +----+--------------+-------------+---------------------------+-----------------------+---------------------+
  </code></pre>
  
  <p>Where rows 2 and 3 represent results where there are no translations.</p>
  
  <p>But I only get 1 row result - the top one with id 2 (because there currently only is one translation row in the table <code>translations</code>).</p>
  
  <p>I've tried various combinations of LEFT and RIGHT JOIN in my query, but nothing seems to help. Am I on track or do I need to completely rethink?</p>