On Thu, 2005-02-17 at 12:08, Albert Padley wrote:
> I have the following 2 tables:
>
> CREATE TABLE `division_info` (
> `id` int(11) NOT NULL auto_increment,
> `division` varchar(50) NOT NULL default '',
> `spots` int(11) NOT NULL default '0',
> PRIMARY KEY (`id`),
> KEY `division` (`division`),
> KEY `spots` (`spots`)
> ) TYPE=MyISAM
>
> CREATE TABLE `team_info` (
> `id` int(14) NOT NULL auto_increment,
> `division` varchar(50) NOT NULL default '',
> `application` varchar(9) NOT NULL default 'No',
> PRIMARY KEY (`id`),
> KEY `division` (`division`),
> ) TYPE=MyISAM
>
> I'm running the following query:
>
> SELECT division_info.division AS 'division', COUNT(team_info.division)
> AS 'count', division_info.spots as 'spots' FROM division_info LEFT JOIN
> team_info ON division_info.division = team_info.division WHERE
> application='ACCEPTED' GROUP BY division_info.division
>
> This query runs fine. However, it only returns divisions where there is
> at least 1 ACCEPTED team. I also need to show divisions where there are
> spots but not teams have yet been ACCEPTED.
>
> A little direction would be appreciated.
>
> Thanks.
>
> Al Padley
SELECT division_info.division AS 'division', COUNT(team_info.division)
AS 'count', division_info.spots as 'spots' FROM division_info
LEFT ==>> OUTER <<== JOIN
team_info ON division_info.division = team_info.division WHERE
application='ACCEPTED' GROUP BY division_info.division
--
- michael dykman
- [EMAIL PROTECTED]
--
MySQL General Mailing List
For list archives: http://lists.mysql.com/mysql
To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
