Link to home
Start Free TrialLog in
Avatar of VBBRett
VBBRett

asked on

How do you do a sql query where something is not equal to a value in one of the fields?

I am trying to do a sql query.  The query I am trying to do is the following,

SELECT        UserId, UserName
FROM            aspnet_Users
WHERE        UserName is not ="admin"

But it doesn't seem to work.  How do I get this to work?
ASKER CERTIFIED SOLUTION
Avatar of pollock_d
pollock_d

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of VBBRett
VBBRett

ASKER

That's what I thought, but SQL Server 2005 in Visual Studio 2008 is giving me problems with that statement.  They suggest the following:

SELECT        UserId, UserName
FROM            aspnet_Users
WHERE        (UserName <> N'admin')

Why is that?  What's with the N?
Here is a list of additonal operators in case you ever need those too.

=    Equal
<>  Not equal
>    Greater than
<    Less than
>=  Greater than or equal
<=   Less than or equal
Avatar of VBBRett

ASKER

So why does SQL 2005 do it this way in the query builder?

SELECT        UserId, UserName
FROM            aspnet_Users
WHERE        (UserName <> N'admin')
N'some string' -- Defines that you are specifying a nvarchar/nchar string and not a varchar string.
The N converts the string to unicode, which is what nvarchar means. It's the same as varchar, but in Unicode (2 bytes).

Is the UserName field an nvarchar field? If not, you probably don't need the N. However, you do need the single quotes instead of double quotes.

This will probably work:

SELECT        UserId, UserName
FROM            aspnet_Users
WHERE        (UserName <> 'admin')  -- not "admin"
Avatar of kretzschmar
alternative

SELECT        UserId, UserName
FROM            aspnet_Users
WHERE        (UserName != 'admin')  -- not "admin"

meikl ;-)
If your query "looks right" (any of the above suggestions should work, I prefer to use  !=  instead of   <> ), and you know there are records that should be returned, but you get none back... don;t overlook the SCHEMA.  If the Schema that owns the data (or the view) is not the same as your default Schema, you shoudl preface your query with the proper Schema, like:
SELECT   UserId, UserName
   FROM   [dbo].[aspnet_Users]
 WHERE   (UserName != 'admin')  -- not "admin"

Of course, you need to have the permission to use a schema that is not your default Schema.
Just a note, we usually uppercase each end of the where clause so that there are no case sensitivy issues

SELECT   UserId, UserName
   FROM   [dbo].[aspnet_Users]
 WHERE   (UPPER(UserName) != UPPER('admin'))
>uppercase each end
which would kill any use if an associated index . . .