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 <[EMAIL PROTECTED]> > >To: Albert Padley <[EMAIL PROTECTED]> > >CC: "\"MySQL List\"" <mysql@lists.mysql.com> > >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 > > - [EMAIL PROTECTED] > > > > > >-- > >MySQL General Mailing List > >For list archives: http://lists.mysql.com/mysql > >To unsubscribe: > >http://lists.mysql.com/[EMAIL PROTECTED] > > > > _________________________________________________________________ > Want to block unwanted pop-ups? Download the free MSN Toolbar now! > http://toolbar.msn.co.uk/ -- - michael dykman - [EMAIL PROTECTED] -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/[EMAIL PROTECTED]
