Can't relate 2 varchar with the same value

Question

I have these 2 tables

f_data
(
 id (int, null),
 name(varchar(255), null),
 control (int, null),
)

id name control
1255 ,ALMONTE JIMENEZ ADALGISA , 1131238601
92 ,ENCARNACION PAMELA , 1131237483
3376 ,FELIZ LUIS MANUEL , 1131240995
688 ,HERRAND MARIÑE DIOMEDES , 1131238666
1887 ,JOSE ALBERTO MATOS , 1131240215

est_data
(
id(int, null),
name(varchar(255), null),
firstname(varchar(255), null),
lastname(varchar(255), null),
)

id name firstname lastname
201201255 ,ADALGISA ,ALMONTE ,JIMENEZ ,
201200092 ,PAMELA ,ENCARNACION , ,
201223376 ,LUIS MANUEL ,FELIZ , ,
201200688 ,DIOMEDES ,HERRAND ,MARIÑE ,
201201887 ,JOSE ALBERTO ,MATOS , ,

and this SQL code

select *
from est_data 
where  
  CASE 
   WHEN 
    lastname  = ' ' 
   then 
    ltrim(rtrim(firstname)) + ' ' + ltrim(rtrim(name))   
   ELSE 
    ltrim(rtrim(firstname))   + ' ' + ltrim(rtrim(lastname))+' '+ltrim(rtrim(name)) 
   END in 
        (Select ltrim(rtrim(name)) From f_data where id = 1887)

The result of this:

CASE 
   WHEN 
    lastname  = ' ' 
   then 
    ltrim(rtrim(firstname)) + ' ' + ltrim(rtrim(name))   
   ELSE 
    ltrim(rtrim(firstname))   + ' ' + ltrim(rtrim(lastname))+' '+ltrim(rtrim(name)) 
   END

is 'JOSE ALBERTO MATOS'

and the result of this:

Select ltrim(rtrim(name)) From f_data where id = 1887

is 'JOSE ALBERTO MATOS'

But the code returns 0 rows, event when the names are the same. What might be the problem here?

note: we are using SQL Server 2000

Answer 1

Look very carefully at your data. In f_data you have a name value of "JOSE ALBERTO MATOS". In est_data you have a name "JOSE", a firstname "ALBERTO", and a lastname "MATOS".

Now look at your query on est_data. You're combining FIRSTNAME-LASTNAME-NAME, which in this case will be "ALBERTO MATOS JOSE", which does not match the value in f_data. You're not ordering the names the same way in both places.

If you don't believe me, just run this simple script to recreate the problem:

create table #f_data
(
 id int,
 name varchar(255),
 control int
);

create table #est_data
(
id int,
name varchar(255),
firstname varchar(255),
lastname varchar(255)
);

insert into #f_data(id, name, control) values (1887, 'JOSE ALBERTO MATOS', 1);

insert into #est_data(id, name, firstname, lastname) values (1887, 'JOSE', 'ALBERTO', 'MATOS');

Select ltrim(rtrim(name)) From #f_data where id = 1887;

select CASE 
   WHEN 
    lastname  = ' ' 
   then 
    ltrim(rtrim(firstname)) + ' ' + ltrim(rtrim(name))   
   ELSE 
    ltrim(rtrim(firstname))   + ' ' + ltrim(rtrim(lastname))+' '+ltrim(rtrim(name)) 
   END
from #est_data;

select *
from #est_data 
where  
  CASE 
   WHEN 
    lastname  = ' ' 
   then 
    ltrim(rtrim(firstname)) + ' ' + ltrim(rtrim(name))   
   ELSE 
    ltrim(rtrim(firstname))   + ' ' + ltrim(rtrim(lastname))+' '+ltrim(rtrim(name)) 
   END in 
        (Select ltrim(rtrim(name)) From #f_data where id = 1887);