Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2010
    Posts
    11

    Return columns where bit value is 1

    Hi there,

    The logic is slipping me right now...

    Have a table with multiple rows, some columns that are bit (0,1).

    Code:
    INSERT INTO UserAccessRights (
        UserID, AllowPhone, AllowTelevision, AllowComputer, AllowVisitation)
    	SELECT 434, 1, 0, 1, 1 UNION ALL
    	SELECT 512, 0, 0, 1, 0 UNION ALL
    	SELECT 982, 0, 1, 1, 1
    Now, I'd like to run the query one user at a time. I'm missing something here, as I can't quite figure out how to only reutrn the results the where the value in the column = 1.

    My pseudocode query would be something like...
    Code:
    select uar.userID, sc.columnName
    from UserAccessRights uar
    JOIN sc.syscolumns sc ON ?? = ??
    where uar.userID = 982
    and [sc.columnname] = 1
    and I would hope to return something like..

    Code:
    982, AllowTelevision, AllowComputer, AllowVisitation
    Can anyone point me in the right direction..?

    Thanks!

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,553
    There are two general ways of going about this. One is to create a table of the flags, and a mapping table between the users and the flags. This would give you an output of
    Code:
    982    AllowTelevision
    982    AllowComputer
    982    AllowVisitation
    The other way is to define the flags as one big integer field, and use bitwise logic to determine what the flags are. This gets a bit more complex, as you then need somewhere to store the definitions of the flags. You would also likely end up with the same output.

  3. #3
    Join Date
    Feb 2004
    Location
    In front of the computer
    Posts
    14,970
    Your schema design is what is making this hard. If you switch to a three-table, normalized schema then this becomes a lot easier.

    -PatP
    In theory, theory and practice are identical. In practice, theory and practice are unrelated.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •