Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AI" in the equal to operation

Just use the following syntax to collate on the fly when joining tables with different collations. I integrate systems, so I have to do this a lot.

select * from [Product] p join [category] c 
on  
c.[Name] collate SQL_Latin1_General_CP1_CI_AS 
= 
p.[Name] collate SQL_Latin1_General_CP1_CI_AS

USE master;
GO
ALTER DATABASE PRATAP
COLLATE Latin1_General_CI_AS_KS_WS ;
GO

--Verify the collation setting.
SELECT name, collation_name
FROM sys.databases
WHERE name = N' PRATAP ';
GO