Query Help

2010-10-27 Thread Nuno Mendes 

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=arch...@jab.org

RE: Problem with sql sintax

2003-12-20 Thread Nuno Mendes 
 
I'm using 4.0.17. Isn't this SQL supported in this version?

Nuno Mendes


-Original Message-
From: Colleen Dick 
Sent: Saturday, December 20, 2003 3:15 PM
To: Nuno Mendes
Cc: [EMAIL PROTECTED]
Subject: Re: Problem with sql sintax

What version you using?
Subselects are not supported in any except the latest

Nuno Mendes wrote:
> The sql bellow is giving a sql sintax error:
> 
> select
> cities.id,
> cities.name,
> (
>   select
>   count(*)
>   from images
>   where ( images.city_id = cities.id )
> ) as number_of_images
> from cities
> 
> Can you point me what am I doing wrong? Thank you!
> 
> Nuno



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]

Problem with sql sintax

2003-12-20 Thread Nuno Mendes 
The sql bellow is giving a sql sintax error:

select
cities.id,
cities.name,
(
  select
  count(*)
  from images
  where ( images.city_id = cities.id )
) as number_of_images
from cities

Can you point me what am I doing wrong? Thank you!

Nuno



-- 
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe:http://lists.mysql.com/[EMAIL PROTECTED]