Hello Assume two tables: parents and children.
: CREATE TABLE `parents` ( `id` smallint(5) unsigned NOT NULL auto_increment, `name` varchar(30) default NULL, PRIMARY KEY (`id`) ) CREATE TABLE `children` ( `id` smallint(5) unsigned NOT NULL auto_increment, `parent_id` smallint(5) unsigned NOT NULL, `name` varchar(30) default NULL, PRIMARY KEY (`id`) Suppose I want to find how many children each parent has, AND how many children each parent has whose name begins with 'k'. SELECT parents.name, count(children.id) as `total children` FROM children JOIN parents ON children.parent_id = parents.id GROUP BY parents.name; +---------+----------------+ | name | total children | +---------+----------------+ | John | 3 | | Mary | 3 | | Susan | 3 | | Tabatha | 3 | | Vernon | 2 | +---------+----------------+ SELECT parents.name, count(children.id) as `total children whose names begin with K` FROM parents JOIN children ON children.parent_id = parents.id WHERE children.name LIKE 'k%' GROUP BY parents.name; +---------+-----------------------------------------+ | name | total children whose names begin with K | +---------+-----------------------------------------+ | John | 2 | | Mary | 2 | | Vernon | 2 | +-------- +-----------------------------------------+ Is there a way to get all this information in one query? I also wonder if it's possible to return rows for which the count(*) is zero, like so: +---------+-----------------------------------------+ | name | total children whose names begin with K | +---------+-----------------------------------------+ | John | 2 | | Mary | 2 | | Vernon | 2 | | Susan | 0 | | Tabatha | 0 | +-------- +-----------------------------------------+ SELECT REPEAT( "Thank you! ", 1000); -- David Mintz http://davidmintz.org/ The subtle source is clear and bright The tributary streams flow through the darkness
_______________________________________________ New York PHP Community MySQL SIG http://lists.nyphp.org/mailman/listinfo/mysql NYPHPCon 2006 Presentations Online http://www.nyphpcon.com Show Your Participation in New York PHP http://www.nyphp.org/show_participation.php
