Please help

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2005 » T-SQL (SS2K5) » Please help

Please help

Rate Topic

Display Mode

Topic Options

Author

Message

Prakash-485822

Posted Wednesday, January 30, 2008 6:21 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 21, 2010 5:46 AM
Points: 26, Visits: 110

Dear All,

I have a two table Users && Roles

Roles contains following columns
Roleid, Rolename,InboxEnabled

Users contains
Userid,Region,Branchid,Roles

The Data in the Roles Table is
Admin -Admin -y
Enduser-EndUser-n
Dev -Developer-y
Network-Network-y

The data in the users table is
Ravi-North-Delhi-Admin,Dev,Network,
Raju-North-Delhi-Dev,Enduser,

select Roleid from Roles where Inboxenabled ='y' will return me Admin and Dev roles

when i pass this query as a condition to users table i have to get output as Ravi

Anyone please help me to solve this query

Post #449323

Andras Belokosztolszki

Posted Wednesday, January 30, 2008 6:32 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, January 26, 2012 5:26 AM
Points: 1,367, Visits: 1,585

This is a good example of bad table design. You should not store the role membership in a single column as a list. Is this something you could change?

If not, then you could create a user defined function to check membership condition, you could use a condition like: Roles + ',' LIKE '%'+@therolename+',%', or a table valued function that returns a row for each of the role in a string, and use crossapply. The second of the above is the one that is the easiest to add quickly, but the best could be to fix your design.

Regards,
Andras

Andras Belokosztolszki, MCPD, PhD
GoldenGate Software

Post #449330

Prakash-485822

Posted Wednesday, January 30, 2008 6:40 AM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 21, 2010 5:46 AM
Points: 26, Visits: 110

Thanks for your reply,

Is there any possiblity with any corelated query or subquery not by assinging to a variable

Post #449339

Adam Haines

Posted Wednesday, January 30, 2008 8:03 AM

SSCrazy

Group: General Forum Members
Last Login: Today @ 1:29 PM
Points: 2,278, Visits: 3,011

select Roleid from Roles where Inboxenabled ='y' will return me Admin and Dev roles

How will this only return Admin and Dev, if networking is 'y' in your test data? The returned data should be Admin, Dev, and networking.. correct?

I agree with Andras that table redesign is the best and most effiecient option. If you must use the current design, you can do something like this.

SELECT UserId
FROM users a INNER JOIN roles b ON a.roles LIKE '%' + b.roleid + '%'
WHERE b.Inboxenabled ='y'
GROUP BY UserId
HAVING COUNT(b.roleid) = 
	(SELECT COUNT(RoleId) 
	 FROM @roles
	 WHERE Inboxenabled ='y')

My blog: http://jahaines.blogspot.com

Post #449416

Prakash-485822

Posted Wednesday, January 30, 2008 9:35 PM

SSC Rookie

Group: General Forum Members
Last Login: Monday, June 21, 2010 5:46 AM
Points: 26, Visits: 110

Thanks a lot it is working

Post #449792

« Prev Topic | Next Topic »

Permissions