MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:SGreen Date:February 17 2005 5:43pm
Subject:Re: JOIN Problem
View as plain text  
Michael Dykman <michael@stripped> 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
>  - michael@stripped
> 

<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
Thread
JOIN ProblemAlbert Padley17 Feb
  • Re: JOIN ProblemMichael Dykman17 Feb
    • Re: JOIN Problemmel list_php17 Feb
      • Re: JOIN ProblemMichael Dykman17 Feb
    • Re: JOIN ProblemSGreen17 Feb
  • Re: JOIN ProblemSGreen17 Feb
    • Re: JOIN ProblemAlbert Padley17 Feb