title: Left join with other table with where clause
body: <p>I have 2 tables in MySQL database that I would like to join, where I would like to contain all results from table1.</p>
  <p>My tables looks like this:</p>
  <p><code>table1</code></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>name1</td>
  </tr>
  <tr>
  <td>2</td>
  <td>name2</td>
  </tr>
  <tr>
  <td>3</td>
  <td>name3</td>
  </tr>
  <tr>
  <td>4</td>
  <td>name4</td>
  </tr>
  <tr>
  <td>5</td>
  <td>name5</td>
  </tr>
  <tr>
  <td>6</td>
  <td>name6</td>
  </tr>
  <tr>
  <td>7</td>
  <td>name7</td>
  </tr>
  <tr>
  <td>8</td>
  <td>name8</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p><code>table2</code></p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>table1_id</th>
  <th>myfield</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>3</td>
  <td>test1</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2</td>
  <td>test2</td>
  </tr>
  <tr>
  <td>3</td>
  <td>1</td>
  <td>test1</td>
  </tr>
  <tr>
  <td>4</td>
  <td>4</td>
  <td>test2</td>
  </tr>
  <tr>
  <td>5</td>
  <td>5</td>
  <td>null</td>
  </tr>
  <tr>
  <td>6</td>
  <td>2</td>
  <td>null</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>What I am trying to achieve is to get a table which contains all the rows from <code>table1</code> and only the data that is joined from <code>table2</code>.</p>
  <p>This is my query:</p>
  <pre><code>select * from table1 as t1
  left join table2 as t2 on t1.id = t2.table1_id
  where myfield=&quot;test1&quot;
  group by t1.id
  </code></pre>
  <p>But this is not what I want to achieve.</p>
  <p>What I want to achieve is to get all records from <code>table1</code> and to have all related records from <code>table2</code> where <code>table2.myfield=&quot;test1&quot;</code>. And for the other for <code>table2.mytable</code> to have null (if they do not fulfil <code>table2.myfield=&quot;test1&quot;</code>).</p>
  <p>Any help is much appreciated!</p>
  <p>Thanks!</p>