MySQL Lists are EOL. Please join:

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

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