title: Cannot resolve the collation conflict between "SQL_Latin1_General_CP1_CI_AS" and "Latin1_General_CI_AS" in the equal to operation
body: <p>Need help here. I have 2 questions based on this query.</p>
  
  <pre><code>SELECT cdd.FieldID,cdd.PlanTypeID,pt.IsFinancial            
  FROM ClientDataDictionary cdd
      INNER JOIN #tblPlanTypes pt ON cdd.PlanTypeID = pt.PlanTypeID
      INNER JOIN ClientDataDictionaryFieldType cddftype
      ON cdd.FieldId = cddftype.FieldID AND cdd.PlanTypeId = cddftype.PlanTypeID
      WHERE cdd.ClientId = @ClientID AND cdd.IsHidden = 0
      AND cddftype.FieldTypeID = 4 
      AND cddftype.FieldID = cddftype.ParentFieldIDSectionTitle 
      AND  (SELECT COUNT(cddftype2.FieldID)
                FROM ClientDataDictionaryFieldType  cddftype2
                Where cddftype2.ParentFieldIDSectionTitle = cdd.FieldID
                AND cddftype2.PlanTypeID = cdd.PlanTypeID
                AND EXISTS(Select tbl.FieldID From #tblSelectedFields tbl
                            Where tbl.FieldID = cddftype2.FieldID AND tbl.PlantypeID =cdd.PlanTypeID COLLATE SQL_Latin1_General_CP1_CI_AS)
                ) != 0 COLLATE SQL_Latin1_General_CP1_CI_AS
  </code></pre>
  
  <ol>
  <li>Where do I place my <code>COLLATE</code> keyword in this query? I know that temp tables have different collate "type".</li>
  <li>I tried using <code>COLLATE DATABASE_DEFAULT</code> however, the error still occurs. Will this resolve the issue?</li>
  </ol>
  
  <p>Please help me here. Thanks!</p>
  
  <p>[UPDATE 1]
  <code>ClientID</code> is set to <code>UNIQUEIDENTIFIER</code> data type.</p>
  
  <p>[UPDATE 2]
  here are the table definitions</p>
  
  <p><code>#tblPlanTypes</code></p>
  
  <pre><code>PlanTypeId nvarchar(50),  
  PlanType nvarchar(max),  
  Prefix NVARCHAR(10),  
  IsFinancial BIT,  
  TotalCount BIGINT,  
  PlanCount BIGINT  
  </code></pre>
  
  <p><code>ClientDataDictonary</code></p>
  
  <pre><code>[ClientDataDictionaryId] [uniqueidentifier] NOT NULL ROWGUIDCOL CONSTRAINT      [DF_ClientDataDictionary_ClientDataDictionaryId] DEFAULT (newid()),
  [ClientId] [uniqueidentifier] NOT NULL,
  [PlanTypeId] [uniqueidentifier] NULL,
  [FieldId] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
  [FieldText] [nvarchar] (max) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [FieldType] [nvarchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [DefaultValue] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [HelpText] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [IsHidden] [bit] NULL CONSTRAINT [DF_ClientDataDictionary_IsHidden] DEFAULT ((0)),
  [IsSummable] [bit] NULL,
  [IsRequired] [bit] NULL,
  [IsContractRenewal] [bit] NULL,
  [HasAlert] [bit] NULL CONSTRAINT [DF_ClientDataDictionary_HasAlert] DEFAULT ((0)),
  [AlertMessage] [nvarchar] (512) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [CentralLanguageId] [uniqueidentifier] NOT NULL,
  [LookupTypeName] [varchar] (500) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [RowVersion] [timestamp] NULL,
  [LastUpdated] [datetime] NULL CONSTRAINT [DF_ClientDataDictionary_LastUpdated] DEFAULT (getutcdate()),
  [Sequence] [int] NULL
  </code></pre>
  
  <p><code>ClientDataDictionaryFieldType</code></p>
  
  <pre><code>[ClientDataDictionaryFieldTypeID] [uniqueidentifier] NOT NULL CONSTRAINT [DF_ClientDataDictionaryFieldType_ClientDataDictionaryFieldType] DEFAULT (newid()),
  [PlanTypeID] [uniqueidentifier] NULL,
  [FieldID] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
  [FieldTypeID] [int] NULL,
  [ParentFieldIDSectionTitle] [nvarchar] (255) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
  </code></pre>
  
  <p><code>#tblSelectedFields</code></p>
  
  <pre><code>FieldID nvarchar(255),  
  PlanTypeID UniqueIdentifier,  
  FieldText nvarchar(MAX),  
  sequence int,  
  FieldType nvarchar(100),  
  IsFinancial bit  
  </code></pre>
  
  <p>[UPDATE 3]</p>
  
  <p>Tried the suggestion by @Mack, but a new error emerged. <code>Expression type uniqueidentifier is invalid for COLLATE clause.</code> This is caused by the @ClientID having a data type of uniqueidentifier. Any suggestions?</p>