Inserting collums data from 3 different tables into new table

Question

I have 3 table, table: "db.Users" has columns: UserId, FirstName, Lastname, Email table: "db.Roles" has columns: RolesId, RoleName Table: "db.UserRoles" Has columns: UserId, RolesId And I would like to Insert the information from the top 3 tables into a new existing table named Table: "db.UsersWithRoles" with 5 columns: UserId, FirstName, Lastname, Email, RoleName I have tried to use Inner join but it is not really what i was looking for because i want to add it to an existing table so i can read it with C# mvc and post it on my website I am not really sure how I am able to achive this.

satheesh d · Answer

This is the query to Join the three tables with the necessary fields. SELECT USR.UserId,USR.FirstName,USR.Lastname,USR.Email,RO.RoleName FROM db.Users USR JOIN db.USERROLES URSRO ON URSRO.UserId = USR.UserId JOIN db.Roles RO ON RO.RolesId = URSRO.RolesId

Jignesh Kumar · Answer

Hi, You can check using below query. If table is exist then will return result else it will not return SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N '[dbo].[ AspNetUsers ]' ) AND type in (N 'U' ) for other table also you can check changing table name. If table are there then you can use your select statement.

osyris zosar · Answer

I am sorry i am new to SQL Question: Am i able to acces a uncreated table by only using Select FirstName, LastName, Email, RoleId from AspNetUsers Inner Join AspNetUserRoles ON AspNetUsers.Id = AspNetUserRoles.UserId; if so how do i acces that table since it is not physically created

Jignesh Kumar · Answer

Hi, As per Table structure you already have user and role mapping then you no need new tble at all. When ever you need you get require user details and role based on your UserRoles table. If you still want then use below two store procedure one for get require detail and one for insert in new table. Create StoreProcedure to GetUserDetails and user role using below procedure. Call from your Mvc Method you will get your all five column then call save method to store in from you controller save method, Procedure to Get detail for user GetUserAndRoleById Create Procedure GetUserAndRoleById @UserId int AS ( select usr.UserId, FirstName, Lastname, Email, RoleName from Users usr inner join UserRoles urole on usr.userId = urole.userId inner join Roles rl on urole.RolesId = rl.RolesId where usr.UserId= @UserId ) Save Data Using below proceure Create Procedure SaveUserRoleMapping @UserId int , @FirstName varchar (100), @Lastname varchar (100), @Email varchar (100), @RoleName varchar (100) AS BEGIN if Not Exists( select UserId from UsersWithRoles where UserId = @UserId and RoleName =@RoleName) BEGIN insert into UsersWithRoles (UserId, FirstName, Lastname, Email, RoleName) values (@UserId, @FirstName, @Lastname, @Email, @RoleName) END ELSE BEGIN update UsersWithRoles set FirstName=@FirstName, Lastname = @Lastname, Email= @Email where UserId = @UserId and RoleName =@RoleName END END