Query Help

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


--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/mysql?unsub=gcdmg-mysql-2@m.gmane.org

Re: Query Help

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.ht ml

--
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=gcdmg-mysql-2@m.gmane.org