Case Sensitive join TSQL
<p>I am at a bit of a standstill here. I have a simple left outer join to a table that is returning an ID.</p>

<p>My code is as </p>

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

<p>My result set is as follows:</p>

<pre><code>**TenantID | Name      | Name2          | TenantNo | CashName**

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

999      |MyShop     | John's Shop    | 12345    |John's shop
</code></pre>

<p><strong>My Issue:</strong>  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.</p>

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

<p>Thanks!</p>

<p>Adding Table information below, please assume all columns are trimmed of whitespace.</p>

<p>tMoney</p>

<pre><code>CashNumnbr |  CashName
102504       Bill's Place     

102374       Tom's Shop      

12345        John's Shop

12345        John's shop
</code></pre>

<p>Tenants</p>

<pre><code>   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        
</code></pre>

<p>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?</p>