Return columns where bit value is 1

justintoo1 · #1 (**permalink**) 11-20-12, 20:23

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!

MCrowley · #2 (**permalink**) 11-21-12, 09:38

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.

Pat Phelan · #3 (**permalink**) 11-21-12, 14:44

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

Internet Services

Web Development

Digital Marketing

Consumer Tech