join 2 tables case sensitive upper and lower case

Question

I have 2 tables and need to get result on brand code.

In the database i have for example 2 different brands but their code is the same (only difference in lower and upper case). For example:

code Name ab Nike AB Adidas

How to inner join 2 tables on code to get this 2 separately?

Right now after inner join i get total sum of this 2.

SELECT Code, BrandName, Count(*) QTY, SUM(Price) TOTAL
FROM A
INNER JOIN B
ON A.Code=B.Code
GROUP BY Code, BrandName

This query will give me wrong result as it does not join sensitively on upper and lower case.

Please help :)

Answer 1

Since you use a collation that is case insensitive and want to differentiate on case try using the collate keyword with a suitable case-sensitive collation:

INNER JOIN B
ON A.Code COLLATE Latin1_General_CS_AS_KS_WS  = B.Code COLLATE Latin1_General_CS_AS_KS_WS