SQLize Online / PHPize Online  /  SQLtest Online

Create table UserRole ( RoleId int not null Primary key, Role nvarchar(50) ) Create table Users ( UserSID Int identity (1,1) Primary key, UserID varchar(50) , ManagerSID int foreign key REFERENCES users(UserSID), RoleId Int foreign key REFERENCES UserRole(RoleId), Isactive bit ) insert UserRole (RoleID, Role) values(1,'PM') insert UserRole (RoleID, Role) values(2,'PA') insert UserRole (RoleID, Role) values(3,'PC') insert users select 'User1', NULL,1,1 union select 'User2', NULL,1,1 union select 'User3', NULL,2,1 union select 'User4', NULL,2,1 union select 'User5', NULL,1,1 union select 'User6', NULL,2,1 union select 'User7', NULL,3,1 union select 'User8', NULL,3,1 union select 'User9', NULL,2,1 union select 'User10', NULL,1,1 union select 'User11', NULL,1,1 union select 'User12', NULL,2,1 union select 'User13', NULL,3,1 union select 'User14', NULL,2,1 union select 'User15', NULL,1,1 union select 'User16', NULL,2,1 union select 'User17', NULL,2,1 union select 'User18', NULL,2,1 union select 'User19', NULL,3,1 update Users set managersid = (select usersid from users where userid='user2') where userid = 'user1' update Users set managersid = (select usersid from users where userid='user3') where userid = 'user2' update Users set managersid = (select usersid from users where userid='user4') where userid = 'user3' update Users set managersid = (select usersid from users where userid='user5') where userid = 'user4' update Users set managersid = (select usersid from users where userid='user6') where userid = 'user5' update Users set managersid = (select usersid from users where userid='user7') where userid = 'user6' update Users set managersid = (select usersid from users where userid='user10') where userid = 'user7' update Users set managersid = (select usersid from users where userid='user10') where userid = 'user8' update Users set managersid = (select usersid from users where userid='user10') where userid = 'user9' update Users set managersid = (select usersid from users where userid='user10') where userid = 'user10' select * from userrole select * from Users /* question: write a query to retreive For each role how many users have manager, and how many users don't have manager the output should be like the below: Role UsersWithmanager UsersWithoutmanager PA 4 5 PC 2 2 PM 4 2 */