Post reply

SQL Syntax for newbie...

  • October 1, 2007 at 1:32 pm

    #92738

    I have a Cross Reference table that I'm storing UserRoles in so a user may have many records in this table depending on how many roles they have in the application. When writing my stored procedure for logging in, I'd like to have one of the columns list the roles in a comma delimited string.

    I'm not sure off hand how to write this SQL and not sure of exact keywords to search around to find the solution. I'm assuming it would be something like this...

    Select u.UserID, (subquery to return comma-delimited string) as User Roles FROM Users...

    I'm not sure what my subquery needs to be...

    For example a standard select against my Cross Refernce table is returning two records, how do I only return one record with these 2 values as the comma-delimited string

    I hope this question makes sense...please let me know your thoughts...

    Kyle

  • October 1, 2007 at 2:04 pm

    #737872

    I would return a recordset/datatable of multiple rows instead, if it were me;

    something like

    ]SELECT UserRoles.UserId,UserRoles.RoleId,UserRoles.RoleName from UserRoles where UserRoles.UserID In (SELECT UserId from Users where UserId=12)

    Or

    SELECT UserRoles.UserId,UserRoles.RoleId,UserRoles.RoleName from UserRoles

    INNER JOIN Users On UserRoles.UserID = Users.UserID WHERE Users.UserId=12

    something like that might return say 3 roles, of "User", "Accounting" and "Sales", so I could iterate thru the set to determine what to display, or what gets enabled, etc.,

    for me, I'd prefer to use a loop to go thru a recordset/set of datarows instead of a custom loop to split() the value you anticipate retreiving.

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 1, 2007 at 2:23 pm

    #737884

    So your statement would return something like this...

    UserID RoleID Role

    1 1 admin

    1 2 member

    and then instead of doing something in SQL just loop thru it in my actual code?

    I guess I thought that seemed like I was returning unnecessary records. While in this case it's only 2 but if a user had 10 different roles, I thought it would be faster to perform whatever action in my stored proc to only return 1 record (that had the 10 roles in a comma-delimited string) and save having to loop thru the dataset.

    I only know enough about SQL to do the basics so if the performance hit is the same here then I'll gladly just loop thru a dataset

  • October 1, 2007 at 2:44 pm

    #737893

    not sure which programming language you are using, but having a recordset/datatable with Userid, RoleId and RoleName might be more flexible;there might be other items you'd want handy in the same set besides the three we identified so far.

    it would be easy to create a function that checks for a role against the recordset or datatable for a specific role or roleid,ie IsAdmin(datatable,"Admin") as boolean

    it seems more convenient to me that you might need that information when saving or updating data. a comma delimited list , at some point, is going to require soem sort of loop to process, so why not leave it in an object you already know how to loop thru (recordset or datarows)

    Lowell

    --help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

  • October 1, 2007 at 2:51 pm

    #737897

    I'm using .NET and I was looking to put it into comma-delimited list as I'm creating a FormsAuthenticationTicket for the user. I hadn't thought of any other uses yet so maybe that would be the way to go, but right now I'm just trying to set the roles for FormsAuthentication

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

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