title: Join 3 tables with 2 where
body: <p>I would like to join three tables with one condition per join (2 conditions) but I am having problems on the second join.</p>
  <p>My tables are:</p>
  <p>clients</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>name</th>
  <th>surname</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>Lois</td>
  <td>Smitm</td>
  </tr>
  <tr>
  <td>2</td>
  <td>Mike</td>
  <td>Williams</td>
  </tr>
  <tr>
  <td>3</td>
  <td>Jonh</td>
  <td>Brown</td>
  </tr>
  <tr>
  <td>4</td>
  <td>Dustin</td>
  <td>Jones</td>
  </tr>
  <tr>
  <td>5</td>
  <td>Robert</td>
  <td>Johnson</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>transactions</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>client_id</th>
  <th>date</th>
  <th>amount</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>2022-12-10</td>
  <td>350</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2</td>
  <td>2022-12-11</td>
  <td>350</td>
  </tr>
  <tr>
  <td>3</td>
  <td>3</td>
  <td>2022-12-12</td>
  <td>210</td>
  </tr>
  <tr>
  <td>4</td>
  <td>4</td>
  <td>2022-12-13</td>
  <td>200</td>
  </tr>
  <tr>
  <td>5</td>
  <td>5</td>
  <td>2022-12-14</td>
  <td>250</td>
  </tr>
  <tr>
  <td>6</td>
  <td>1</td>
  <td>2022-12-15</td>
  <td>450</td>
  </tr>
  <tr>
  <td>7</td>
  <td>5</td>
  <td>2022-12-16</td>
  <td>850</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>clients_history</p>
  <div class="s-table-container">
  <table class="s-table">
  <thead>
  <tr>
  <th>id</th>
  <th>client_id</th>
  <th>action</th>
  <th>date</th>
  </tr>
  </thead>
  <tbody>
  <tr>
  <td>1</td>
  <td>1</td>
  <td>register</td>
  <td>2022-01-20</td>
  </tr>
  <tr>
  <td>2</td>
  <td>2</td>
  <td>register</td>
  <td>2022-01-21</td>
  </tr>
  <tr>
  <td>3</td>
  <td>3</td>
  <td>register</td>
  <td>2022-06-19</td>
  </tr>
  <tr>
  <td>4</td>
  <td>4</td>
  <td>register</td>
  <td>2022-03-10</td>
  </tr>
  <tr>
  <td>5</td>
  <td>5</td>
  <td>register</td>
  <td>2022-02-20</td>
  </tr>
  </tbody>
  </table>
  </div>
  <p>I would like a query that returns name and surname of those clients that have been registered between January 22 and March 22 (both included) AND the AVERAGE amount of their transactions is between 300 and 500.</p>
  <p>I have this code but it is not working:</p>
  <pre><code>SELECT name, surname
  FROM (
      SELECT c.id, name, surname
      FROM clients c
      LEFT JOIN clients_history ch
      ON c.id = ch.client_id
      WHERE ch.action = &quot;register&quot;
      AND ch.date &gt;= '2022-01-01' AND ch.date&lt;= '2022-03-31'
  ) AS r
  LEFT JOIN transactions t
  ON r.id = t.client_id
  WHERE avg(t.amount) &gt;= '300' AND  avg(t.amount) &lt;= '500'
  GROUP BY t.client_id 
  </code></pre>