Using IN Statement with SQL 2005

Discussion in 'C#' started by craigedmonds, Nov 30, 2007.

0
  1. #1
    Okay, not sure if I am being dumb here but.....

    I have a column called Permissions and it contains the following rows..

    1
    1
    31,42
    1,39

    The numbers in the 4 rows represent userid's.

    What I need to do is be able to pull off any records from that column that have usrid 1 in it.

    I have just tried.....

    select * from documents where (permissions IN ('"&strUserid&"'))

    However this ONLY gives me the first two rows not row 1,2 and 4.

    Anyone out there handy with IN Statements? (or should I beusing anotehr statement for this?

    Any help will be welcome.
     
    craigedmonds, Nov 30, 2007 IP
  2. CMS Wrapper

    CMS Wrapper Peon

    Messages:
    20
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #2
    You should try this:

    select * from documents where (permissions IN ('"&"%"&strUserid&"%"&"'))

    The % signs added at the beginning and the end of the strUserid variable tells the sql server that you want any rows that have the data in your variable, even if there is more data in the row, at the beginning of the element as well as at the end.
     
    CMS Wrapper, Nov 30, 2007 IP
  3. teraeon

    teraeon Peon

    Messages:
    40
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    0
    #3
    It looks like you're trying to do a many to 1 relationship through the use of strings which is inefficient. The best way is to create a join table.

    A join table connects your two tables. Each record in the join table points to one record in your user table and one record in your permissions table.

    Then when you want to get permissions for a user you just do the following

    SELECT * FROM Permissions, UserPermissions WHERE Permissions.PermissionID = UserPermissions.PermissionID AND UserPermissions.UserID = 1

    This way is much more efficient, and reduces the number of operations the DB has to do to compare against a string.
     
    teraeon, Nov 30, 2007 IP
  4. urstop

    urstop Peon

    Messages:
    224
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #4
    This is good then the original query, but the problem is if the column has 21 then it will return that row as well as it has 1, but I think the user is looking for an ID of 1 and not 1 in other IDs.

     
    urstop, Dec 2, 2007 IP
  5. urstop

    urstop Peon

    Messages:
    224
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #5
    This might not work as the colum can have more than one ID seperated by commas.

     
    urstop, Dec 2, 2007 IP