Case Sensitive join TSQL

Question

I am at a bit of a standstill here. I have a simple left outer join to a table that is returning an ID.

My code is as

Select distinct TenantID
,Name
,Name2
,TenantNumber
,Cashname 
From Tenants 
LEFT OUTER JOIN tCash 
on TenantNumber = CashNumber 
and tMoney.CashName = Tenants.Name2

My result set is as follows:

**TenantID | Name      | Name2          | TenantNo | CashName**

100      |MyShop     | John's shop    | 12345    |John's shop

999      |MyShop     | John's Shop    | 12345    |John's shop

My Issue: for all intents and purposes, "John's shop" IS different from "John's Shop" - I am correctly joining my money table on the TenantNo and then on Name2, but name 2 is different by Case.

Question: Is there any way to differentiate a join based on case sensitivity? I would not want to use UPPER or LOWER due to the fact that it would ruin the case on reporting.

Thanks!

Adding Table information below, please assume all columns are trimmed of whitespace.

tMoney

CashNumnbr |  CashName
102504       Bill's Place     

102374       Tom's Shop      

12345        John's Shop

12345        John's shop

Tenants

   TenantID | Name     | Name2            |TenantNumber 

  1         |MyShop    | John's Shop     | 12345

  2         |MyShop    | John's shop     | 12345

  3         |Shoppee   |  Bill's Place   | 102504        

  4         | Shop2    | Toms Shop      | 102374        

Since I want to join to get the correct TenantID for an AR report, I would want to make sure I am always bringing in the correct tenant. If the case is different, is there anything I can write to differentiate a situation like John's Shop?

Answer 1

The problem is that in the second row of your results "John's Shop" shouldn't have matched "John's shop"?

You can use a case sensitive collation.

This is probably best achieved by altering the collation of the columns involved to allow index use but you can also do it at run time with an explicit COLLATE clause as below.

SELECT DISTINCT TenantID,
                Name,
                Name2,
                TenantNumber,
                Cashname
FROM   Tenants
       LEFT OUTER JOIN tCash
         ON TenantNumber = CashNumber
            AND tMoney.CashName = Tenants.Name2 COLLATE Latin1_General_100_CS_AS 

The comments about joining on id instead of name are likely correct though and would negate the need to do this at all.