bit comparison in sql

[simplyJ]

Is it possable to use bit comparing in SQL statements? If I use bit numbers(ie.. &H000001) for security permissions, how would I do a search for 2 different levels? I have about 5 different security settings that users can have. I need to do a search and retrieve anyone with any of these 5 settings. Any ideas for the sql?


I can find information about the basic comparisons and operations used in sql, but I don't see anything about bit comparison in sql.

here is an sql example

GROUP_MEMBER = &H000001

&quot;SELECT MAX(username) as username, MAX(fname) as fname, MAX(lname) as lname FROM (groupmembers gm left JOIN users us ON gm.gmUserID = us.dbid) WHERE gmGroupID = &quot; & groupID & &quot; AND ((gmStatus and &quot; & GROUP_MEMBER & &quot <> 0)&quot; & &quot; GROUP by gmStatus, gmStartDate&quot;

This syntax will run, but it returns the wrong results.

Does this work in a sql statement?
((gmStatus and &quot; & GROUP_MEMBER & &quot <> 0)

 

[tlbroadbent]

Lookup the Bitwise operators in SQL BOL - also available at

http://msdn.microsoft.com/library/en-us/tsqlref/ts_oa-oz_3qpf.asp

Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[tlbroadbent]

I apologize for the other post. It would be appropriate in a SQL Server forum. The operators are the same in ANSI SQL as in T-SQL but I shouldn't have referred you to SQL Server references. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.

 

[simplyJ]

I appreciate the information...

However, I am new to TSQL. I created the following SQL statement and was not very successful. I get results, but it looks like it return all the records in the table for the group selected.

SELECT MAX(gmID) as gmID, MAX(gmUserID) as gmUserID, MAX(gmGroupID) as gmGroupID, MAX(gmStartDate) as gmStartDate, MAX(gmEmailPrime) as gmEmailPrime, MAX(gmEmailAlt) as gmEmailAlt, MAX(gmPreferences) as gmPreferences, MAX(gmStatus) as gmStatus, MAX(username) as username, MAX(fname) as fname, MAX(lname) as lname FROM (groupmembers gm left JOIN users us ON gm.gmUserID = us.dbid) WHERE gmGroupID = 18 AND (gmStatus & 8) GROUP by gmStatus, gmStartDate


I noticed on the website that you suggested the words GO and USE were put into the statement. Is there any place, or could you tell me, what I need to do to make this query. I guess, to be honest, I have just always coded in SQL. I haven't paid attention to if it is ANSI or TSQL or etc... For this reason, I don't know what to do with these examples.


Thanks again

 

[tlbroadbent]

Since you've posted the same question in an Access forum, I assume you are working in Access. SQL in Access is nether T-SQL nor fully ANSI comlpiant as you may have noticed. Terry L. Broadbent
FAQ183-874 contains tips for posting questions in these forums.
NOTE: Reference to the FAQ is not directed at any individual.