On 10/27/2010 6:55 AM, Nuno Mendes wrote:
I have 3 tables: (1) Companies, (2) locations and (3) employees:
CREATE TABLE `companies` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(75) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `locations ` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(75) NOT NULL,
`company_id` int(11) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
CREATE TABLE `employees` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(5) NOT NULL,
`location_id` int(11) NOT NULL,
UNIQUE KEY `id` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
How do I retrieve list of all companies with total number of locations
and total number of employees? The query bellow is the closest I could
get to what I want but it's not quite there.
SELECT
companies.name,
Count(locations.id) AS locations_count,
Count(employees.id) AS employees_count
FROM
companies
LEFT JOIN locations ON (companies.id = locations.company_id)
LEFT JOIN employees ON (locations.id = employees .locations_id)
GROUP BY
companies.id
Thank you!
Nuno Mendes
Have you looked at the WITH ROLLUP query modifier?
http://dev.mysql.com/doc/refman/5.1/en/group-by-modifiers.html
--
Shawn Green
MySQL Principal Technical Support Engineer
Oracle USA, Inc.
Office: Blountville, TN
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=arch...@jab.org
