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

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