[eluser]Tim Brownlaw[/eluser]
I'm probably way off track here but I can't resist.
Code:
SELECT g.*,count(u.id) as user_count FROM `group` g JOIN user_group u ON u.group_id=g.id GROUP BY u.group_id;
Using the above on the following tables...
Code:
CREATE TABLE `group` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(16) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
Code:
CREATE TABLE `user_group` (
`id` int(11) NOT NULL auto_increment,
`name` varchar(16) NOT NULL,
`group_id` int(11) NOT NULL,
PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1 DEFAULT CHARSET=latin1
With the data...
Code:
INSERT INTO `group` (name) values ('Hockey'),('Soccer'),('IceSkating');
INSERT INTO user_group (name,group_id) values ('Nigel',1),('Fred',1),('George',2),('Sam',3);
Results in
Code:
id name user_count
============================
1 Hockey 2
2 Soccer 1
3 IceSkating 1
All tested and working...
Now I can get to bed
