Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Please help Expand / Collapse
Author
Message
Posted Wednesday, January 30, 2008 6:21 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:33 AM
Points: 27, Visits: 116
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
Posted Wednesday, January 30, 2008 6:32 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen 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
Posted Wednesday, January 30, 2008 6:40 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:33 AM
Points: 27, Visits: 116
Thanks for your reply,

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

Post #449339
Posted Wednesday, January 30, 2008 8:03 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Wednesday, March 26, 2014 10:34 AM
Points: 2,278, Visits: 3,046

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
Posted Wednesday, January 30, 2008 9:35 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: 2 days ago @ 6:33 AM
Points: 27, Visits: 116
Thanks a lot it is working
Post #449792
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse