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.