LEFT OUTER JOIN with GROUP BY? Wrong rows showing up.

[Adamantus]

Hi. This is my first post. I have a little problem with using LEFT OUTER JOIN with GROUP BY.

I have two tables, customer and projects. Each customer can have many projects. I want to display the customers regardless of 0 projects and display the number of projects next to each row of customers. I have:

Customer 1 = 20 projects with this foreign key
Customer 2 = 0 projects with this foreign key
Customer 3 = 0 projects with this foreign key
Customer 4 = 0 projects with this foreign key

SELECT c.*, date_format(c.date_added, '%D %M %Y') as c_date_added, count(p.c_id) as project_count, 0 as time, c.c_id as cust_id
FROM (`customers` c)
  LEFT OUTER JOIN `projects` p ON `c`.`c_id` = `p`.`c_id`
GROUP BY `p`.`c_id` LIMIT 10;

This query produces two rows, customer 1 and customer 2. Where are customers 3 and 4? Only customer one has a foreign key match inside projects table so it is no a case of not joining.

Here is sql if you want it:

CREATE TABLE `customers` (
  `c_id` int(11) NOT NULL auto_increment,
  `cust_name` varchar(100) default NULL,
  `description` text,
  `date_added` datetime default NULL,
  PRIMARY KEY  (`c_id`)
)

CREATE TABLE `projects` (
  `p_id` int(11) NOT NULL auto_increment,
  `c_id` int(11) default NULL,
  `project_name` varchar(100) default NULL,
  `project_desc` text,
  `date_added` datetime default NULL,
  `parent_id` int(11) default NULL,
  PRIMARY KEY  (`p_id`)
)

[r937]

This query produces two rows, customer 1 and customer 2. Where are customers 3 and 4?

your query is fine

there must be something else going on that you haven't revealed

are you sure there is no WHERE clause?

[Adamantus]

your query is fine

there must be something else going on that you haven't revealed

are you sure there is no WHERE clause?

I did change it for simplicity so you could read through it easily. The complete query is this but with / without the where claus is the same result:

SELECT *, date_format(c.date_added, '%D %M %Y') as c_date_added, count(p.c_id) as project_count, 0 as time, c.c_id as cust_id
FROM (`customers` c)
  LEFT OUTER JOIN `projects` p ON `c`.`c_id` = `p`.`c_id`
WHERE c.c_id!=1
GROUP BY `p`.`c_id`
LIMIT 10

I definitely have 4 customers. Only one has a link inside projects (customer 1). For some reason customer 2 comes back, then no others.

P.s. Thanks for replying.

[swampBoogie]

group 
    by c.c_id

Since p.c_id is null for three records this is considered as one group.

[Adamantus]

group 
    by c.c_id

Since p.c_id is null for three records this is considered as one group.

That was it, thank you swampBoogie! It was the fact that I was just looking at the name column. The other 3 rows must have condensed into one and just given it the field names of the first, making it look as though customers 3 and 4 where missing. This will seriously help this and future projects, I've been scared of left/right joins for years.

SOLUTION
So the solution is that tables with a left join and group by will group the null rows into one row. So alter the group by to refer to the LEFT joined or the RIGHT joined table.