MySQL Lists are EOL. Please join:

List:General Discussion« Previous MessageNext Message »
From:Albert Padley Date:February 17 2005 5:58pm
Subject:Re: JOIN Problem
View as plain text  
On Feb 17, 2005, at 10:34 AM, SGreen@stripped wrote:

>
>
> Albert Padley <apadley@stripped> wrote on 02/17/2005 12:08:31 PM:
>
>  > 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
>  >
>  >
>
> You are very, very close. You used the LEFT JOIN (correct choice) but 
> you eliminated all of the rows from your division table without any 
> accepted teams when you said WHERE application='ACCEPTED'. That's why 
> you aren't getting a good count across all of your divisions.
>
> What I think you were trying to do was to tell how many teams have 
> accepted within a division, across all divisions. That means you want 
> to "count" 'ACCEPTED' teams but not teams that do not exist or teams 
> that have some other application status, right?
>
> I have reworked your query a bit and I think I answered the question 
> you had and I also tried to demonstrate how to get at some other 
> information at the same time.
>
> SELECT d.division AS 'division'
>         , d.spots as 'spots'
>         , COUNT(t.division) AS 'total_team_count'
>         , SUM(IF(t.application = 'ACCEPTED',1,0)) as
> 'teams_accepted'
>         , SUM(IF(t.application <> 'ACCEPTED',1,0)) as 
> 'teams_not_accepted'
> FROM division_info d
> LEFT JOIN team_info t
>         ON d.division = t.division
>  GROUP BY d.division, d.spots
>
> Using the aggregating functions like COUNT() and SUM() in this way, we 
> are building a crosstab query (also called a pivot table). There are 
> many other articles in this thread's archive that can help you 
> understand how to build those types of queries with MySQL.
>
> By eliminating your WHERE clause and moving your condition into a 
> SUM(IF()), we allowed all of the rows from the RIGHT side of the LEFT 
> JOIN (even those with all null values) to appear in the results and 
> thanks to the IF() we only count (by adding up the 1's) those rows 
> with the values we want to find.
>
> HTH,
> Shawn Green
>  Database Administrator
>  Unimin Corporation - Spruce Pine

Shawn,

Thanks. This was just right. Once again, you have gone beyond the 
initial question and not only provided the correct answer, but an 
explanation that helps me better understand the "why" behind the query. 
Much appreciated. Thanks again.

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