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)
