Compare data between different collations

Today I was trying to join two fields from different tables. One of the table in the query was from a linked sql server with a different collation. As such when runing this comparision I was getting the error Cannot resolve the collation conflict between “Latin1_General_BIN” and “Latin1_General_CI_AS” in the equal to operation. I am sure many of us would have got this error from time to time. The way I resolved this was simple. Use collate database_default in the comparision statement.

Hence you statement will look like this:

Select * From table1, table2

Where x = y

AND (table1.departmentid =  left(table2.dept,2) collate database_default)