Join 2 Tables on a Column with Differing Collations in SQL

SELECT 'SQL_Latin1_General_CP1_CI_AS' AS 'Collation',
    COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'CodePage') AS 'CodePage',
    COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'LCID') AS 'LCID',
    COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'ComparisonStyle') AS 'ComparisonStyle',
    COLLATIONPROPERTY('SQL_Latin1_General_CP1_CI_AS', 'Version') AS 'Version'
UNION ALL
SELECT 'Latin1_General_CI_AS' AS 'Collation',
    COLLATIONPROPERTY('Latin1_General_CI_AS', 'CodePage') AS 'CodePage',
    COLLATIONPROPERTY('Latin1_General_CI_AS', 'LCID') AS 'LCID',
    COLLATIONPROPERTY('Latin1_General_CI_AS', 'ComparisonStyle') AS 'ComparisonStyle',
    COLLATIONPROPERTY('Latin1_General_CI_AS', 'Version') AS 'Version'
GO

--Clean up previous query
IF EXISTS(SELECT 1 FROM sys.tables WHERE Name = 'MyTable1')
    DROP TABLE MyTable1

IF EXISTS(SELECT 1 FROM sys.tables WHERE Name = 'MyTable2')
    DROP TABLE MyTable2

--Create a table using collation Latin1_General_CI_AS and add some data to it
CREATE TABLE MyTable1
(
    ID INT IDENTITY(1, 1),
    Comments VARCHAR(100) COLLATE Latin1_General_CI_AS
)
INSERT INTO MyTable1 (Comments) VALUES ('Chiapas')
INSERT INTO MyTable1 (Comments) VALUES ('Colima')

--Create a second table using collation SQL_Latin1_General_CP1_CI_AS and add some data to it
CREATE TABLE MyTable2
(
    ID INT IDENTITY(1, 1),
    Comments VARCHAR(100) COLLATE SQL_Latin1_General_CP1_CI_AS
)
INSERT INTO MyTable2 (Comments) VALUES ('Chiapas')
INSERT INTO MyTable2 (Comments) VALUES ('Colima')

--Join both tables on a column with differing collations
SELECT * FROM MyTable1 M1
INNER JOIN MyTable2 M2 ON M1.Comments collate SQL_Latin1_General_CP1_CI_AS = M2.Comments collate SQL_Latin1_General_CP1_CI_AS

GO