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