Different results when query table by different columns

Question

Different results when query table by different columns

Kevin Wieand

Grasshopper

Points: 11
More actions
September 27, 2010 at 9:54 am

#387873

Can anyone help me with this?
I have a table with an ID, last name, and first name which are all varchar. There are other columns in the table also.
My table looks like this:
ID Lastname Firstname
12345 Smith Robert
12346 Smith Robert
12347 Smith Robert
If I query the table where ID = 12345, I get the record for Robert Smith.
If I query the table where lastname = 'Smith' and Firstname = 'Robert', I get some Robert Smith records, but not 12345 which I am looking for? Someone had suggested to me to drop the indexes on that table and recreate them, but that did not change anything.
Thanks.

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply

SQLQuest29 SSCrazy Eights Points: 8264 More actions · Answer 1

Kevin Wieand (9/27/2010)
Can anyone help me with this?
I have a table with an ID, last name, and first name which are all varchar. There are other columns in the table also.
My table looks like this:
ID Lastname Firstname
12345 Smith Robert
12346 Smith Robert
12347 Smith Robert
If I query the table where ID = 12345, I get the record for Robert Smith.
If I query the table where lastname = 'Smith' and Firstname = 'Robert', I get some Robert Smith records, but not 12345 which I am looking for? Someone had suggested to me to drop the indexes on that table and recreate them, but that did not change anything.
Thanks.

Is it case sensitive ? What is the server and db collation set to ? Version of sql server ?

Cheers !

______________________________________________________________________________________________________________________________________________________________________________________
HTH !
Kin
MCTS : 2005, 2008
Active SQL Server Community Contributor 🙂

SQLRNNR SSC Guru Points: 281334 More actions · Answer 2

Have you checked for spaces in the names?

Have you tried to run your query with a like operator rather than an equality operator?

Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events

pankushmehta Ten Centuries Points: 1217 More actions · Answer 3

What happens when you query the table with where Ltrim(Rtrim(lastname)) = 'Smith' and Ltrim(Rtrim(Firstname)) = 'Robert'?

dietacai Grasshopper Points: 19 More actions · Answer 4

I have the same problem. How to solve it ? thanks in advace !

acai berry diet