Mssql 2005 - help in query building

[bhakti_thakkar]

Hi all,
i have a table (Users_Roles_T) where in i assign different roles to a user for e.g user_id 1 has three roles 1,2,5 (Admin , Accounts , Inspector)

Users_Roles_T :
User_ID | ROle_ID | RoleName_VC
1 1 Admin
1 2 Accounts
1 5 Inspector

Now there is another table in which i store all the menus that are applicable for a user

for eg : Menu_T
Menu_ID | MenuName_VC | Roles_ID (storing comma seperated in this field)
1 Sales 1,5,4,2,
2 New projects 5,4,9,3,
3 Registrations 8,12,10


in the above eg Menu Sales and New Projects both are available to user "1" as his combined roles have the respective menus. But Registration Menu is not applicable to user "1" as it doesnt fall in to any roles of this user(1,2,5)

How will i build the query to extract all the MEnu_ID from Menu_T table for a Role
Please help
Thanks
Bhakti

[ckeyrouz]

select distinct m.Menu_ID
from Menu_T m, Users_Roles_T u
where m.roles_id = u.roles_id
and u.user_id = 1

[Old Pedant]

Wrong answer from CKeyrouz.

Because Bhakti is making the bad MISTAKE of using a delimited list in a DB field, the query CKeyrouz gave will never find any matches, unless one Roles_ID just happens to only have a single number.

CKeyrouz can be forgiven for expecting a GOOD db design instead of this mistake.

Bhakti: You *really* need ANOTHER TABLE in your DB.

You SHOULD have something like this:

TABLE : Menu_T
Menu_ID | MenuName_VC
1 | Sales
2 | New projects
3 | Registrations

TABLE: MenuRoles
Menu_ID | Role_ID
1 | 1
1 | 5
1 | 4
1 | 2
2 | 5
2 | 4
etc.

*NOW* CKeyrouz's answer is correct, if we just change the table name:

select distinct m.Menu_ID
from MenuRoles m, Users_Roles_T u
where m.role_id = u.role_id
and u.user_id = 1


Using a delimited list in a single DB field is almost always a mistake. Clearly it is a mistake in this case.

[ckeyrouz]

I did not pay attention to the fact that the values are comma separated.
I apologize for that.

[Old Pedant]

CKeyrouz: Patently not your fault. You wouldn't expect a mistake like that!