CODEWITHSUNDEEP
mysqlsql
dave823
dave823

Reputation: 1211

SQL(MYSQL) Select column names where sum of that column > 0

I am having trouble constructing a query, and need help. Consider the following MYSQL tables.

user table

username
tuser
tman
tester

usergrouplink table

username|groupname
tuser   |grp1
tuser   |grp2

group table

groupname|reporter|provisioner|manager
grp1     |0       |0          |1
grp2     |0       |1          |1
grp3     |1       |0          |0

The group table links a group name (grp1) to a role (reporter, provisioner, manager) based on bit flags (0 or 1)

I need a query that can return a list of role names given a username. For example, given user 'tuser' is in grp1 and grp2, return a role list of provisioner and manager

Role
provisioner
manager

I have tried the following, and it seems to work, but I must find a way to simplify it:

select distinct Role, 'Roles' from
(
    select 'Reporter' as Role from
    (
        select * from Groups g where g.name in 
        (
            select groupname from UserGroupLink l where l.username='tuser'
        )
    ) x
    where x.reporter>0

    union

    select 'Provisioner' as Role from
    (
        select * from Groups g where g.name in 
        (
            select groupname from UserGroupLink l where l.username='tuser'
        )
    ) x
    where x.provisioner>0

    union

    select 'Manager' as Role from
    (
        select * from Groups g where g.name in 
        (
            select groupname from UserGroupLink l where l.username='tuser'
        )
    ) x
    where x.manager>0
) z

The above query works, but it compares against the username 'tuser' three times, once for each role. I need to modify the query, if possible, to only have the username string literal in a single place (for Java JAAS security if anyone is curious). Does anyone know if this is possible?

Upvotes: 0

Views: 91

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270523

If you don't mind putting the roles into a single row, you can do:

select concat_ws(',',
                 (case when reporter > 0 then 'Reporter'
                       when Provisioner > 0 then 'Provisioner'
                       when Manager > 0 then 'Manager'
                  end)
                ) as roles
from user u join
     usergrouplink ugl
     on u.tuser = ugl.tuser join
     groups g
     on ugl.groupname = g.groupname
where u.name = 'tuser';

If you have control, you should consider normalizing the groups table by creating a GroupRoles table with one role per group and one row per role.

EDIT:

If you need multiple rows, you can still do that. It is just easier to put it one one row:

select (case when reporter > 0 and n.n = 1 then 'Reporter'
             when Provisioner > 0 and n.n = 2 then 'Provisioner'
             when Manager > 0 and n.n = 3 then 'Manager'
        end) as role
from user u join
     usergrouplink ugl
     on u.tuser = ugl.tuser join
     groups g
     on ugl.groupname = g.groupname cross join
     (select 1 as n union all select 2 union all select 3
     ) n
where u.name = 'tuser'
having role is not NULL;

Note that this makes use of the having clause extension in MySQL. You can use it without a group by and use a column alias for the condition. Otherwise the logic would need a subquery or complicated where statement.

Upvotes: 1

Related Questions