You haven't told how many members are there.
Anyway... i've worked out something with a colleague at work...
Given your tables:
*CREATE TABLE `list_problem_members` ( `id` int(11) NOT NULL
auto_increment, `member` varchar(11) NOT NULL default '', PRIMARY KEY
(`id`) );*
**
*CREATE TABLE `list_problem_actions` ( `id` int(11) NOT NULL
auto_increment, `action` varchar(11) NOT NULL default '', PRIMARY KEY
(`id`) );*
**
*CREATE TABLE `list_problem_ma` ( `id` int(11) NOT NULL
auto_increment, `member` int(11) unsigned NOT NULL default '0', `action`
int(11) unsigned NOT NULL default '0', PRIMARY KEY (`id`) );*
We came up with this:
*SELECT b.`action`, count(b.`action`)
FROM `list_problem_ma` AS a
RIGHT JOIN `list_problem_ma` AS b ON b.`member`=a.`member`
WHERE a.`action`=3 AND b.`action`!=3
GROUP BY b.`action`*
**
This will give you a result as:
*action - count(b.`action`)
1 - 3
2 - 1*
This shows that action 1 was performed 3 times and action 2 one time !
*Is this what you wanted ?*
In the area of optimizations... a key in *`list_problem_ma`* table on the
columns *`member` *AND* `action`* will do fine because MySQL will not scan
NR_OF_ROWS(`list_problem_ma`) * NR_OF_ROWS(`list_problem_ma`) but only
NR_OF_ROWS(`list_problem_ma`) * 1 .... You will still get: Using where;
Using index; Using temporary; Using filesort ... but i believe that's no way
arround that !
--
Gabriel PREDA
Senior Web Developer
