Post reply

Please help

  • January 30, 2008 at 6:21 am

    #182379

    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

  • January 30, 2008 at 6:32 am

    #773234

    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

  • January 30, 2008 at 6:40 am

    #773242

    Thanks for your reply,

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

  • January 30, 2008 at 8:03 am

    #773308

    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

  • January 30, 2008 at 9:35 pm

    #773616

    Thanks a lot it is working

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply