I have an application serving multilevel permissions and roles
I have this HIERARCHY:
Country
.... area
........ City
............ Association
................ Centre
.................... school
........................ Class
this HIERARCHY i call it "EntityLevels"
| ID | Name | ParentID |
|----|-------------|----------|
| 1 | Country | Null |
| 2 | Region | 1 |
| 3 | City | 2 |
| 4 | Association | 3 |
| 5 | Center | 4 |
| 6 | School | 5 |
| 7 | Class | 6 |
I also have a group table that means Jobs
| ID | Name | EntityLevels |
|----|--------------------|--------------|
| 1 | CountryAdmins | 1 |
| 2 | Region Supervisors | 2 |
user table is as follows:
| ID | Name |
|----|-------|
| 1 | User1 |
| 2 | User2 |
I now have a UserJobs or UserGroups table
| ID | UserID | GroupdID | EntityID |
|----|--------|----------|----------|
| 1 | User1 | 1 | 1 |
| 2 | User2 | 2 | 2 |
| 3 | User3 | 4 | 38 |
now the problem is how can I get each user and his answers depending on what he is under his level
for eaxmple:
user1 must have all roles and permissoins in order to see all users under his level, because he is in Group (1) and Group1, he is on EntityLevel (1), which he is at the country level.
- , . -
;WITH MyCTE AS (
SELECT T1.ID, UserId, 0 AS TreeLevel, CAST(T1.ID AS VARCHAR(255)) AS TreePath FROM UserJobs T1
inner join EntityLevel el on t1.GroupId = el.Id WHERE EL.ParentID IS NULL
UNION ALL
SELECT T2.ID, T2.UserId, TreeLevel + 1, CAST(TreePath + '.' + CAST(T2.ID AS VARCHAR(255)) AS VARCHAR(255)) AS TreePath
FROM UserJobs T2
inner join EntityLevel el on T2.GroupId = el.Id
INNER JOIN
MyCTE itms ON itms.ID = EL.ParentID
)
SELECT ID, TreeLevel, TreePath
FROM MyCTE
ORDER BY TreePath;
Script DATA
dbfiddle
CREATE TABLE [dbo].[Assocation](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[CityID] [int] NULL,
CONSTRAINT [PK_Assocation] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Center](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[AssociationID] [int] NULL,
CONSTRAINT [PK_Center] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[City](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[RegionID] [int] NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Class](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[SchoolID] [int] NULL,
CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Country](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EntityLevel](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[ParentID] [int] NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Group](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[EntityLevelID] [int] NULL,
CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Region](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[CountryID] [int] NULL,
CONSTRAINT [PK_Region] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[School](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[CenterID] [int] NULL,
CONSTRAINT [PK_School] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Mobile] [varchar](50) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserJobs](
[ID] [int] NOT NULL,
[UserID] [int] NOT NULL,
[GroupID] [int] NOT NULL,
[EntityID] [int] NOT NULL,
CONSTRAINT [PK_UserJobs] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[UserID] ASC,
[GroupID] ASC,
[EntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Assocation] ([ID], [Name], [CityID]) VALUES (1, N'KH', 1)
GO
INSERT [dbo].[Assocation] ([ID], [Name], [CityID]) VALUES (2, N'mkh_ass', 2)
GO
INSERT [dbo].[Center] ([ID], [Name], [AssociationID]) VALUES (1, N'NorthCenter', 1)
GO
INSERT [dbo].[Center] ([ID], [Name], [AssociationID]) VALUES (2, N'SouthCenter', 1)
GO
INSERT [dbo].[City] ([ID], [Name], [RegionID]) VALUES (1, N'Jeddah', 1)
GO
INSERT [dbo].[City] ([ID], [Name], [RegionID]) VALUES (2, N'MakkahCiry', 1)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (1, N'Class1-Ahmed', 1)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (2, N'Class2-omar', 1)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (3, N'class3_khaled', 2)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (4, N'class4_fahd', 2)
GO
INSERT [dbo].[Country] ([ID], [Name]) VALUES (1, N'KSA')
GO
INSERT [dbo].[Country] ([ID], [Name]) VALUES (2, N'UAE')
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (1, N'Country', NULL)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (2, N'Region', 1)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (3, N'City', 2)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (4, N'Association', 3)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (5, N'Center', 4)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (6, N'School', 5)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (7, N'Class', 6)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (1, N'SA', 1)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (2, N'country admin', 1)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (3, N'region admin', 2)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (4, N'region Supervisor', 2)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (5, N'manager', 4)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (6, N'supervisor', 5)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (7, N'teacher', 7)
GO
INSERT [dbo].[Region] ([ID], [Name], [CountryID]) VALUES (1, N'Makkah', 1)
GO
INSERT [dbo].[Region] ([ID], [Name], [CountryID]) VALUES (2, N'Riyadh', 1)
GO
INSERT [dbo].[School] ([ID], [Name], [CenterID]) VALUES (1, N'School1', 1)
GO
INSERT [dbo].[School] ([ID], [Name], [CenterID]) VALUES (2, N'School2', 1)
GO
INSERT [dbo].[School] ([ID], [Name], [CenterID]) VALUES (3, N'School3', 2)
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (1, N'Loai', N'000000')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (2, N'User1', N'1111')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (3, N'User2', N'2222')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (4, N'User3', N'3333')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (5, N'User4', N'4444')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (6, N'user5', N'5555')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (7, N'user6', N'6548')
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (1, 1, 1, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (2, 2, 2, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (3, 3, 3, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (4, 4, 4, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (5, 5, 5, 2)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (6, 6, 6, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (7, 7, 7, 2)
GO
(EDIT): :

Script # 2
CREATE TABLE [dbo].[Assocation](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[CityID] [int] NULL,
CONSTRAINT [PK_Assocation] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Center](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[AssociationID] [int] NULL,
CONSTRAINT [PK_Center] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[City](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[RegionID] [int] NULL,
CONSTRAINT [PK_City] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Class](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[SchoolID] [int] NULL,
CONSTRAINT [PK_Class] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Country](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
CONSTRAINT [PK_Country] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[EntityLevel](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[ParentID] [int] NULL,
CONSTRAINT [PK_Table_1] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Group](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[EntityLevelID] [int] NULL,
CONSTRAINT [PK_Group] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[Region](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[CountryID] [int] NULL,
CONSTRAINT [PK_Region] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[School](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[CenterID] [int] NULL,
CONSTRAINT [PK_School] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[User](
[ID] [int] NOT NULL,
[Name] [nvarchar](50) NULL,
[Mobile] [varchar](50) NULL,
CONSTRAINT [PK_User] PRIMARY KEY CLUSTERED
(
[ID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[UserJobs](
[ID] [int] NOT NULL,
[UserID] [int] NOT NULL,
[GroupID] [int] NOT NULL,
[EntityID] [int] NOT NULL,
CONSTRAINT [PK_UserJobs] PRIMARY KEY CLUSTERED
(
[ID] ASC,
[UserID] ASC,
[GroupID] ASC,
[EntityID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
GO
INSERT [dbo].[Assocation] ([ID], [Name], [CityID]) VALUES (1, N'KH', 1)
GO
INSERT [dbo].[Assocation] ([ID], [Name], [CityID]) VALUES (2, N'mkh_ass', 2)
GO
INSERT [dbo].[Center] ([ID], [Name], [AssociationID]) VALUES (1, N'NorthCenter', 1)
GO
INSERT [dbo].[Center] ([ID], [Name], [AssociationID]) VALUES (2, N'SouthCenter', 1)
GO
INSERT [dbo].[City] ([ID], [Name], [RegionID]) VALUES (1, N'Jeddah', 1)
GO
INSERT [dbo].[City] ([ID], [Name], [RegionID]) VALUES (2, N'MakkahCiry', 1)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (1, N'Class1', 1)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (2, N'Class2', 1)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (3, N'class3', 2)
GO
INSERT [dbo].[Class] ([ID], [Name], [SchoolID]) VALUES (4, N'class4', 2)
GO
INSERT [dbo].[Country] ([ID], [Name]) VALUES (1, N'KSA')
GO
INSERT [dbo].[Country] ([ID], [Name]) VALUES (2, N'UAE')
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (1, N'Country', NULL)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (2, N'Region', 1)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (3, N'City', 2)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (4, N'Association', 3)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (5, N'Center', 4)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (6, N'School', 5)
GO
INSERT [dbo].[EntityLevel] ([ID], [Name], [ParentID]) VALUES (7, N'Class', 6)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (1, N'Country Manager', 1)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (2, N'Region Manager', 2)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (3, N'City Manager', 3)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (4, N'Association Manager', 4)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (5, N'Center Manager', 5)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (6, N'School Manager', 6)
GO
INSERT [dbo].[Group] ([ID], [Name], [EntityLevelID]) VALUES (7, N'Teacher', 7)
GO
INSERT [dbo].[Region] ([ID], [Name], [CountryID]) VALUES (1, N'Makkah', 1)
GO
INSERT [dbo].[Region] ([ID], [Name], [CountryID]) VALUES (2, N'Riyadh', 1)
GO
INSERT [dbo].[School] ([ID], [Name], [CenterID]) VALUES (1, N'School1', 1)
GO
INSERT [dbo].[School] ([ID], [Name], [CenterID]) VALUES (2, N'School2', 1)
GO
INSERT [dbo].[School] ([ID], [Name], [CenterID]) VALUES (3, N'School3', 2)
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (1, N'UserA', N'000000')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (2, N'UserB', N'1111')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (3, N'UserC', N'2222')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (4, N'UserD', N'3333')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (5, N'UserE', N'4444')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (6, N'UserF', N'5555')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (7, N'UserG', N'6548')
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (8, N'UserH', NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (9, N'UserI', NULL)
GO
INSERT [dbo].[User] ([ID], [Name], [Mobile]) VALUES (10, N'UserJ', NULL)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (1, 1, 1, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (2, 2, 2, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (3, 3, 3, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (4, 4, 4, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (5, 5, 5, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (6, 6, 6, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (7, 7, 7, 1)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (8, 8, 2, 2)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (9, 9, 3, 2)
GO
INSERT [dbo].[UserJobs] ([ID], [UserID], [GroupID], [EntityID]) VALUES (10, 10, 4, 2)
GO
?