Two things: firstly the stated objective and the sample query differ
(WHERE division.leagueID???). Secondly I'm sorry that I'm short of time
this morning, otherwise I'd try to develop/illustrate this for you...
May I suggest that you employ the KISS principle: Start off by coding a
simple join between the two tables to list every division and every team
within it - the way that you relate these two tables is a key to the
solution. Also doing that much will locate and correct one problem.
Then try adding the GROUP, and later add back the existing HAVING
clause. That should achieve the stated objective.
Thereafter you may reconsider that you don't even want to have the IF()
in there (I'm thinking, that you're thinking, that it will perform the
table join - but then I might be thinking something that you're not
thinking, and I think and you will also think, that such would be a
Let us know how you get on!
> What I'm trying to do is display a list of division
> names assuming that each division has at least 2 teams
> associated with it.
> Let's start with the table definitions:
> table name #1: division
> | ID | leagueID | name | rank |
> | 1 | 1 | Gold | 1 |
> | 2 | 1 | Silver | 2 |
> | 3 | 1 | Bronze | 3 |
> | 4 | 2 | Level 1 | 1 |
> table name #2: team
> | ID | leagueID | divisionID | name |
> | 1 | 1 | 1 | Vipers |
> | 2 | 1 | 1 | Warthogs |
> | 3 | 1 | 1 | Kings |
> | 4 | 1 | 2 | Ducks |
> I created the following query:
> SELECT division.ID as curID, division.name, SUM(
> IF(team.divisionID = 'curID', 1, 0)) as teamcount
> FROM division, team
> WHERE division.leagueID = '1'
> GROUP BY division.ID
> having teamcount > 1
> This results in zero records.
> During some testing I found that, instead of trying to
> sum based on the "IF(team.divisionID = 'curID'", and
> instead used an actual value, like this:
> "IF(team.divisionID = '1'", that would produce
> results, but it would list every division name and the
> team count would be "3" in each row.
> I hope that was descriptive enough...
> Thanks in advance for your help!
> Do You Yahoo!?
> Try FREE Yahoo! Mail - the world's greatest free email!
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php