title: join three tables in codeigniter with one to many relation
body: <p>I am doing a project in codeigniter.Here I want to join three tables </p>
  
  <p>clients(id, name, email, adminId, campaignId, dateAdded, is_deleted)</p>
  
  <p>campaign(id, name, adminId) and</p>
  
  <p>order(id, name, cost, dateAdded, clientId).</p>
  
  <p>From these tables i want to select(in between two dates) the number of clients added,campaign name and total order cost of a client.When I joined the two tables (clients and campaign) it returns the correct result.</p>
  
  <p>The query I used is</p>
  
  <pre><code>$this-&gt;db-&gt;select('clients.id AS my_client,
      clients.name AS client_name,
      campaign.name AS campaign_name,
      DATE(clients.dateAdded) as client_date,
      COUNT(clients.id) AS num_rows');
  
  $this-&gt;db-&gt;from('clients');
  $this-&gt;db-&gt;where('clients.adminId', $adminId);
  $this-&gt;db-&gt;where('DATE(clients.dateAdded) &gt;=', $from_date);
  $this-&gt;db-&gt;where('DATE(clients.dateAdded) &lt;=', $to_date);
  $this-&gt;db-&gt;join('campaign', 'campaign.id = clients.campaignId', 'left');
  $this-&gt;db-&gt;group_by('campaign_name');
  $query = $this-&gt;db-&gt;get();
  return $query-&gt;result();
  </code></pre>
  
  <p>But when I joined three tables(clients, campaign, order) it is not returning correct result.The relation between client and order is one to many.Ie one client can have more than one order.So it will not give correct value for total number of clients added between two dates.The join query I used to join three tables is </p>
  
  <pre><code>$this-&gt;db-&gt;select('clients.id AS my_client,
      clients.name AS client_name,
      campaign.name AS campaign_name,
      DATE(clients.dateAdded) AS client_date,
      SUM(order.cost) AS order_cost,
      COUNT(clients.id) AS num_rows');
  
  $this-&gt;db-&gt;from('clients');
  $this-&gt;db-&gt;where('clients.adminId', $adminId);
  $this-&gt;db-&gt;where('clients.is_deleted', 0);
  $this-&gt;db-&gt;where('DATE(clients.dateAdded) &gt;=', $from_date);
  $this-&gt;db-&gt;where('DATE(clients.dateAdded) &lt;=', $to_date);
  $this-&gt;db-&gt;join('campaign', 'campaign.id = clients.campaignId', 'left');
  $this-&gt;db-&gt;join('order', 'order.clientId = clients.id', 'left');
  $this-&gt;db-&gt;group_by('campaign_name');
  $query = $this-&gt;db-&gt;get();
  return $query-&gt;result();
  </code></pre>
  
  <p>Can anyone have some idea to do this.Thanks in advance</p>