MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:mel list_php Date:February 17 2005 5:30pm
Subject:Re: JOIN Problem
View as plain text  
Would you mind giving me some additional explanation about outer join?
In the mysql reference book I just found one line saying "left outer join 
syntax exists only for compatibility with odbc".
thanks!

>From: Michael Dykman <michael@stripped>
>To: Albert Padley <apadley@stripped>
>CC: "\"MySQL List\"" <mysql@stripped>
>Subject: Re: JOIN Problem
>Date: Thu, 17 Feb 2005 12:20:44 -0500
>
>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
>
>
>--
>MySQL General Mailing List
>For list archives: http://lists.mysql.com/mysql
>To unsubscribe:    
>http://lists.mysql.com/mysql?unsub=1
>

_________________________________________________________________
Want to block unwanted pop-ups? Download the free MSN Toolbar now!  
http://toolbar.msn.co.uk/

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