If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back  dBforums > Database Server Software > Microsoft SQL Server > Return columns where bit value is 1

LinkBack Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Registered User
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).

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...
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..

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

Reply With Quote
  #2 (permalink)  
Registered User
Join Date: Jan 2003
Location: Massachusetts
Posts: 5,477
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
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.
Reply With Quote
  #3 (permalink)  
Resident Curmudgeon
Join Date: Feb 2004
Location: In front of the computer
Posts: 14,815
Your schema design is what is making this hard. If you switch to a three-table, normalized schema then this becomes a lot easier.

In theory, theory and practice are identical. In practice, theory and practice are unrelated.
Reply With Quote

Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are On