MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Michael Dykman Date:February 17 2005 5:44pm
Subject:Re: JOIN Problem
View as plain text  
The idea of the OUTER JOIN is that it find at least one row for the
joined table, even if the conddtion for that tables fails.. the
resulting row will have all nulls except for the connecting fields.

it guarantees that every row of division_info which is returned by the
first part of the query is represented in the final data set even if
there is no correcponding team_info which matches the join condition.

I'm didn't notice what version of MySQL you are running and I'm not 100%
sure this is supported under MySQL 3.23 (for example) but it certainly
works on my 4.1..  the outer join has been part of ANSI-SQL syntax for
at least 10 years I think.


On Thu, 2005-02-17 at 12:30, mel list_php wrote:
> 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/
-- 
 - 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