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
