Welcome Guest, Not a member yet? Register   Sign In
MySQL COUNT() from two joined tables results in wrong numbers
#1

[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.




Theme © iAndrew 2016 - Forum software by © MyBB