FREE AI-Enhanced Online SQL Server Compiler

Untitled*

You must execute the code again to save those values in the database.

CREATE TABLE [dbo].[groups](
	[gid] [int] NOT NULL,
	[groupname] [varchar](50) NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[roles](
	[roleid] [int] NOT NULL,
	[rolename] [varchar](50) NOT NULL,
	[toplevel] [bit] NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[users](
	[uid] [int] NOT NULL,
	[name] [varchar](50) NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[usergroup](
	[uid] [int] NOT NULL,
	[gid] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[userrole](
	[uid] [int] NOT NULL,
	[roleid] [int] NOT NULL
) ON [PRIMARY]
GO

CREATE TABLE [dbo].[rolegroup](
	[roleid] [int] NOT NULL,
	[gid] [int] NOT NULL
) ON [PRIMARY]
GO
CREATE TABLE [dbo].[rolerole](
	[partentid] [int] NOT NULL,
	[childid] [int] NOT NULL
) ON [PRIMARY]
GO
insert into [users] values (1,'alice')
insert into [users] values (2,'bob')
insert into [users] values (3,'chris')

insert into [groups] values (1,'group 1')
insert into [groups] values (2,'group 2')
insert into [groups] values (3,'group 3')

insert into [roles] values (1,'Role A',1)
insert into [roles] values (2,'Role B',0)
insert into [roles] values (3,'Role C',0)

insert into rolerole values (1,2)

insert into rolegroup values (2,1)
insert into rolegroup values (2,2)
insert into rolegroup values (3,1)
insert into rolegroup values (3,2)

insert into userrole values (1,1)
insert into userrole values (1,3)
insert into userrole values (2,1)
insert into userrole values (3,2)

insert into usergroup values (1,1)
insert into usergroup values (1,2)
insert into usergroup values (1,3)

insert into usergroup values (3,1)
insert into usergroup values (3,2)

select * from groups
inner join usergroup on usergroup.gid = groups.gid
inner join users on users.uid = usergroup.uid
where users.uid = 1

select * from users
left join userrole on userrole.uid = users.uid	
left join roles on roles.roleid = userrole.roleid
left join rolegroup on rolegroup.roleid = userrole.roleid
left join groups on groups.gid = rolegroup.gid
where users.uid = 1

gid	groupname	uid	gid_3	uid_4	name
1	group 1	1	1	1	alice
2	group 2	1	2	1	alice
3	group 3	1	3	1	alice

uid	name	uid_2	roleid	roleid_4	rolename	toplevel	roleid_7	gid	gid_9	groupname
1	alice	1	1	1	Role A	1	NULL	NULL	NULL	NULL
1	alice	1	3	3	Role C	0	3	1	1	group 1
1	alice	1	3	3	Role C	0	3	2	2	group 2

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(1 rows affected)

(3 rows affected)

(3 rows affected)