Correlated subqueries in MySql - how to evaluate outer query first??

Question

Hi all!

I am having problems with subqueries in MySql. I have a table containing user groups. The columns are id, name and the properties with a comment describing each row: (Id is INT, Name VARCHAR, all other TINYINT(1) (boolean that is)

ID   |   Name   |   login   |   post   |   manage
 1     user           1           0           0
 2     poster         1           1           0
 3     admin          1           1           1

My goal is to be able to list the usergroup properties (login, post and manage above) and the number of usergroups that has each property (3, 2 and 1 respectively).

This query works (but obviously counts the login column every time):

SELECT @colname:=cols.column_name,cols.column_comment,
  (SELECT COUNT(*) FROM db.usergroups WHERE login=1) AS num_users
FROM information_schema.columns AS cols 
WHERE TABLE_SCHEMA='db' AND TABLE_NAME='usergroups' AND column_type='tinyint(1)';

This does not work (num_users is always 0)

SELECT @colname:=cols.column_name,cols.column_comment,
  (SELECT COUNT(*) FROM db.usergroups WHERE cols.column_name=1) AS num_users
FROM information_schema.columns AS cols 
WHERE TABLE_SCHEMA='db' AND TABLE_NAME='usergroups' AND column_type='tinyint(1)';

This neither (num_users is always 0)

SELECT @colname:=cols.column_name,cols.column_comment,
  (SELECT COUNT(*) FROM db.usergroups WHERE @colname=1) AS num_users
FROM information_schema.columns AS cols 
WHERE TABLE_SCHEMA='db' AND TABLE_NAME='usergroups' AND column_type='tinyint(1)';

Is there any way to get this to work? That is - to evaluate the outer statement first?

-

Thanks a lot for any help!

/Victor

Answer 1

If I understand your problem correctly, I think this may do what you need:

select
  sum(login) as Num_Login_Groups,
  sum(post) as Num_Post_Groups,
  sum(manage) as Num_Manage_Groups
from db.usergroups;

Alternatively, if you really need 'login', 'post' and 'manage' to appear on the same row as the number of groups with that privilege, you could do this:

select 'login' as Property, sum(login) as Count from db.usergroups
union
select 'post' as Property, sum(post) as Count from db.usergroups
union
select 'manage' as Property, sum(manage) as Count from db.usergroups;

I'm assuming these are the only three properties your table will have. If you want an extensible set of privileges for each user group, I would suggest storing them in a separate table, with one row for each applicable user-privilege pairing.

You might also consider using Name as the primary key of the table, as it is more intuitive and probably needs to be unique anyway.

Answer 2

That is really not the way to use a relational database. You could probably do it using prepared statements...

PREPARE stmt FROM 'SELECT COUNT(*) FROM db.usergroups WHERE ' + col_name + '=1';
-- Execute statement etc...

But the real way to do this dynamically is to separate the columns into rows (i.e. have another table called db.permissiontypes or whatnot, then join it with db.usergroups.)