Click here to Skip to main content
15,883,883 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I want to insert data into my table but the problem is the user id coloumn which is returning multiple values. so what can i do in that case.

What I have tried:

Insert into UserAccess(ClientId,UserId,ParentId,Name,ToolTip,NavigateUrl,UserRole,IsParent,Menu_Order,Status,MenuId) SELECT @ClientId ,(select USR_ID 
from UserDetail WHERE Client_Id=@ClientId AND (',' + USR_Role + ',') LIKE '%,2,%' AND Status=1 AND Emp_Id!=0), ParentId,Name,ToolTip,NavigateUrl,UserRole,IsParent,Menu_Order,Status,Id from UserMenu WHERE UserRole=2 AND Status=1


Problem is the usr_Id coloumn because at first we only select the top 1 user where userid =2 but now our requirments changes so we have to insert the data for every user with userid=2. How can i acheive this?
Posted
Updated 16-Nov-18 6:33am
Comments
Tomas Takac 13-Nov-18 3:10am    
It feels like you need a cross join between UserDetail and UserMenu but I'm not sure. Maybe update your question with sample input and expected output to make it more clear.
akshay_zz 15-Nov-18 11:51am    
Buddy I think you need to update your question. Specially the last paragraph. I can help you if you can update the question a little. Like what you want to say for ever userid = 2 you want to insert userid. That's pretty confusing.

1 solution

You haven't provided the additional information that was requested 3 days ago but I will attempt to demonstrate the solution.

I created some demo tables with this script
create table #user
(
	id int identity(1,1),
	dataitem varchar(10),
	[status] int
)
insert into #user (dataitem, [status]) values
('User1',1), ('User2',1), ('User3',1)

create table #usermenu
(
	id int,
	menudetail varchar(10),
	[user_id] int
)
insert into #usermenu (id, menudetail, [user_id]) values
(1, 'Menu 1', 1), (1, 'Menu 1', 2), (1, 'Menu 1', 3)
So now I have 3 users, all at status 1 and all have access to Menu 1. If I attempt your style of query like this
SELECT (select id from #user where status = 1), id, menudetail from #usermenu
I get an error
Quote:
Msg 512, Level 16, State 1, Line 25
Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression.

You need to be able to join the tables in some way. In my example I would use
select a.id, b.id, menudetail
from #usermenu B
INNER JOIN #user A on A.ID=B.[user_id]
There are a couple of aspects of your code that concern me.
1. The snippet LIKE '%,2,%' implies that you are storing user roles in a comma-separated string. This is very bad design. Instead you should store those roles in another table - that way you don't have to mess around with separating the string or searching it to see which roles are available - it's discussed at length elsewhere. E.g.
create table #UserRoles
(
	[User_Id] int,
	roleNo int
)
I probably wouldn't bother having a separate roles table as that would be taking normalisation a tad too far.
2. You have a table called UserMenu but it appears to have all of the Menu information in it. I would have a table Menu that contains all that data and a separate table that just links User_Ids to the Menu's to which they have access.
 
Share this answer
 

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900