Inner Join matches non-equal fields??????

Hi group,

This morning we had an issue with a simple join between 2 tables e.g. table1 and table2.
Both tables have 1 column called 'fielda' with datatype varchar.

Let's assume the following values:
Table1:
hello
Hello

Table2:
hello

This statement joins the tables:
select a.fielda
from table1 a inner join table2 b on a.fielda = b.fielda

It returns:
hello
Hello

What the $#@#$
Why doesn't it return 1 value:
hello

Any ideas
The outcome is exactly what we want but I would expected to have to use the following:
select a.fielda
from table1 a inner join table2 b on UPPER(a.fielda) = UPPER(b.fielda)

What if I wanted to return all EXACT matches Then what Change collation

Answer this question

Inner Join matches non-equal fields??????

Davy de Kerf
Does anybody know what the default colation is and if this is case sensitive

TIA
Frend Chen
The default collation for your database comes from [model] database if you did not specify [collate] during the database creation. Your table/column will inherit your database's collation unless you, again, specify [collate] during its creation.
ANDY_DANDY
The result is as such because you are using UPPER(FieldName)

If you want the result as single "hello" , Please use query ->

select a.fielda
from table1 a inner join table2 b on a.fielda = b.fielda

Thanks.
graham forbes

You most likely have a case insensitive collation, so you will need to set it to case insensitive for the column/database, or if this is a one time thing, you can do just force the collation:

create table test
( --CI indicates that it is case insensitive
value varchar(10) collate latin1_general_ci_as
)
create table test2
(
value varchar(10) collate latin1_general_ci_as
)
insert into test
select 'Hello'
union all --all or it would
select 'hello'

insert into test2
select 'hello'
go

--case insensitive:
select *
from test
join test2
on test.value = test2.value
returns:

value value
---------- ----------
Hello hello
hello hello

--case sensitive

select *
from test
join test2
on test.value COLLATE latin1_general_cs_as
= test2.value COLLATE latin1_general_cs_as

value value
---------- ----------
hello hello

You can check to collation of your database with:

select databasepropertyex(db_name(),'collation') as collation_name

You can check the collation of your column with:

select column_name, collation_name
from INFORMATION_SCHEMA.COLUMNS
where table_name = 'test'

I wrote a blog about this a while back which might help you change to collation of your table/database. Every column will have to be changed individually if you want to change the collation completely, changing the db collation only changes the default for the database.

http://drsql.spaces.msn.com/blog/cns!80677FB08B3162E4!853.entry
Inner Join matches non-equal fields??????

Answer this question