0

I have a quistion regarding making a view.

I have 3 tables

users:

  • id
  • name
  • email
  • password

permissiongroups:

  • id
  • name
  • viewUsers (tiny int, 1 or 0)
  • updateUsers (tiny int, 1 or 0)
  • createUsers (tiny int, 1 or 0)
  • deleteUsers (tiny int, 1 or 0)
  • (many other perms) (tiny int, 1 or 0)

user_has_permissiongroups:

  • id
  • userId
  • permissiongroupId
  • userIdCreator
  • active

Now what I want is to make a single view that shows what permissions a user has. Say they have a group that can delete users (and all else as 0 (or false)), and another group that can create them (and all else as 0 (or false)), I want a combined row that shows both being a 1 (true) and everything else 0 (false).

Now what I need help with is the creation of the select statement as I have no idea where to begin. I will need to make something that groups by user.

SELECT * 
FROM user_has_permissiongroups
LEFT JOIN permissiongroups
ON user_has_permissiongroups.permissiongroupId = permissiongroups.id
WHERE user_has_permissiongroups.active = 1
GROUP BY userId

Now this gets me almost where I need to be, but it doesn't group the permissions together correctly yet. Does anyone know how I can best do this? Or is this not possible within sql, meaning I have to keep it in the software?

Desired output:

userId viewUsers updateUsers createUsers deleteUsers
1 1 1 1 1
2 1 0 0 0

With tables: permissiongroups:

id name viewUsers updateUsers createUsers deleteUsers
1 test1 1 0 0 0
2 test2 0 1 0 1
3 test3 0 1 1 0

user_has_permissiongroups:

id userId permissiongroupId userIdCreator active
1 1 1 3(not relevant) 1
2 1 2 3(not relevant) 1
3 1 3 3(not relevant) 1
4 2 1 3(not relevant) 1

Please note that there are many more permissions, it would be best if it were possible that it automatically takes all of them.

Regards Dany

Mr Dany
  • 3
  • 6
  • Do you want each permission in a different column? That means you need a pivot. See https://stackoverflow.com/questions/7674786/how-can-i-return-pivot-table-output-in-mysql?noredirect=1&lq=1 – Barmar Dec 29 '22 at 16:19
  • You're using `GROUP BY` without any aggregation fucntion. So you'll just get one row for each user, with permission data from a random row in the group. You can use `GROUP_CONCAT()` to combine the permissions into a single column. – Barmar Dec 29 '22 at 16:21
  • Please add sample data and the desired result. – Barmar Dec 29 '22 at 16:22
  • I indeed want every permission in a different column. I have added some sample data and result. – Mr Dany Dec 29 '22 at 16:55

0 Answers0