Hugh, Thanks so much for the very detalied explanation. You can bet I'll be saving it as reference for future projects. Your solution worked perfectly!
Advertising
As for the divisionID not being needed in both tables. Ultimately, you are correct. I could easily get away with connecting the tables together. But, this project has a lot of pages that deal with one table at a time so I found that it keeps my queries cleaner to have it in both places. It may not be the cleanest code in the world, but it works. Thanks again for your help! John --- Hugh Bothwell <[EMAIL PROTECTED]> wrote: > > 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. > > > > 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 | > > Note: I don't see why leagueID should appear > in both tables; surely one of them is redundant? > Does a division belong to a league? And if so, > surely a team belonging to a division will already > belong to a league by implication? > > > Instead of listing teams by division, I start by > listing each division and its associated teams (if > any): > > SELECT division.name, team.name > FROM division LEFT JOIN team ON division.id = > team.divisionID > > which gets me: > > Gold | Warthogs > Gold | Vipers > Gold | Kings > Silver | Ducks > Bronze | > Level 1 | > > Note that there are no teams for Bronze or Level 1, > but we still get the categories. LEFT JOIN returns > rows with NULL entries when there is no match. > > I can use this to turn the team-existence condition > into a number: > > SELECT division.name, IF(team.name IS NULL,0,1) AS > teams > FROM division LEFT JOIN team ON division.id = > team.divisionID > > which gets me: > > Gold | 1 > Gold | 1 > Gold | 1 > Silver | 1 > Bronze | 0 > Level 1 | 0 > > > The next step is obviously to sum these values: > > SELECT division.name, SUM(IF(team.name IS NULL,0,1)) > AS teams > FROM division LEFT JOIN team ON division.id = > team.divisionID > GROUP BY division.name > > returning: > > Gold | 3 > Silver | 1 > Bronze | 0 > Level 1 | 0 > > and finally a HAVING clause to filter: > > SELECT division.name, SUM(IF(team.name IS NULL,0,1)) > AS teams > FROM division LEFT JOIN team ON division.id = > team.divisionID > GROUP BY division.name > HAVING teams >= 2 > > which leaves: > > Gold | 3 > > > Voila! __________________________________________________ Do You Yahoo!? Try FREE Yahoo! Mail - the world's greatest free email! http://mail.yahoo.com/ -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
