Michael Dykman <[EMAIL PROTECTED]> wrote on 02/17/2005 12:20:44 PM: > 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] > <begin flame> Michael, Please be so kind as to explain WHY you thought your answer was DIFFERENT than the originally posted query? The OUTER keyword is optional in MySQL. That means that "LEFT JOIN" and "LEFT OUTER JOIN" are parsed as the same token. Look at the problem again, remembering that the team_info table is the OUTER table of the JOIN, and see if you can spot the problem. I'll give you another hint, it's a SQL logic issue, not a SQL grammar issue. <end flame> Shawn Green Database Administrator Unimin Corporation - Spruce Pine
