+- CodeIgniter Forums (https://forum.codeigniter.com)
+-- Forum: Archived Discussions (https://forum.codeigniter.com/forumdisplay.php?fid=20)
+--- Forum: Archived Development & Programming (https://forum.codeigniter.com/forumdisplay.php?fid=23)
+--- Thread: MySQL COUNT() from two joined tables results in wrong numbers (/showthread.php?tid=46219)
MySQL COUNT() from two joined tables results in wrong numbers - El Forum - 10-23-2011
[eluser]suntrop[/eluser]
Hi there. I've got three MySQL tables: clients, projects, tasks. In one page I want to display all clients and next to it the sum of all projects and all tasks assigned to this client. But I get wrong numbers from this code:
Code:
$this->db->select('
c.id AS customer_id,
c.name AS customer_name,
c.shorthand AS customer_shorthand,
COUNT(p.id) AS project_count,
COUNT(t.id) AS task_count
');
$this->db->from('customers AS c');
$this->db->join('projects AS p', 'p.customer_id = c.id')->group_by('p.customer_id');
$this->db->join('tasks AS t', 't.customer_id = c.id')->group_by('t.customer_id');
$query = $this->db->get();If I delete either the JOIN (and count) from projects or tasks, it works as expected. But both will result in a wrong count . I think it multiplies the project count with the task count.
What I am doing wrong? I don't get it.