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`)
)