CODEWITHSUNDEEP
mysqlstored-procedures
Anto S
Anto S

Reputation: 2449

For loop in stored procedure

I have three tables, roles, role_privillages and modules:

roles

id name
1  Super admin
2  Sub admin
3  Viewer

role_privillages

id role_id  module_id
1   1       1
2   1       2
3   1       3
4   2       1
5   2       4
6   3       3
7   3       1

modules

id  name
1   Users management
2   Roles Management
3   Content Management
4   Texture Management

After running a procedures, I should get the result as below

id   name           assigned_modules
1    Super Admin    Users management, Roles management, Content Management
2    Sub Admin      Users management, Texture management
3    Viewer         Content Management, User Management

is it possible through stored procedure? This kind of procedure will take too much execution time?

Upvotes: 0

Views: 69

Answers (1)

davek
davek

Reputation: 22915

Something like this should work, which you can define in a view or procedure as needed:-

   select 
       r.id
     , r.name
     , group_concat(m.name) as assigned_modules
   from roles r 
   inner join role_privileges p on r.id = p.role_id
   inner join modules m on m.id = p.module_id
   group by r.id, r.name

So, to answer your questions:

  • yes, it is possible in a stored procedure, or even in a view
  • execution time depends on a number of factors (indexing, amount of data etc.etc.)

Upvotes: 1

Related Questions