title: Cannot resolve collation conflict for column 4 in SELECT statement
body: <p>I am trying to get some SQL to execute but am getting the following error</p>
  
  <pre><code>    Msg 451, Level 16, State 1, Line 1
  Cannot resolve collation conflict for column 4 in SELECT statement.
  </code></pre>
  
  <p>But cannot seem to figure out where the problem is???  Any help would be very much appreciated.</p>
  
  <pre><code>SELECT     MEMBTYPEID.text AS MemberType, MEMBLST.nodeId, MEMBTYPES.Name AS MemberField, 
                        ISNULL(CASE WHEN MEMBTYPES.datatypeID IN
                            (SELECT     NodeId
                              FROM          DBO.CMSDATATYPE
                              WHERE      DBTYPE = 'Nvarchar') THEN MEMBDATA.[dataNvarchar] WHEN MEMBTYPES.datatypeID IN
                            (SELECT     NodeId
                              FROM          DBO.CMSDATATYPE
                              WHERE      DBTYPE = 'Ntext') THEN MEMBDATA.[dataNtext] WHEN MEMBTYPES.datatypeID IN
                            (SELECT     NodeId
                              FROM          DBO.CMSDATATYPE
                              WHERE      DBTYPE = 'Date') THEN CONVERT(NVARCHAR, MEMBDATA.[dataDate]) WHEN MEMBTYPES.datatypeID IN
                            (SELECT     NodeId
                              FROM          DBO.CMSDATATYPE
                              WHERE      DBTYPE = 'Integer') THEN CASE WHEN
                            (SELECT     value
                              FROM          [dbo].[cmsDataTypePreValues]
                              WHERE      datatypenodeid = MEMBTYPES.[dataTypeId] AND id = CONVERT(INT, MEMBDATA.[dataInt])) IS NOT NULL THEN
                            (SELECT     value
                              FROM          [dbo].[cmsDataTypePreValues]
                              WHERE      datatypenodeid = MEMBTYPES.[dataTypeId] AND id = CONVERT(INT, MEMBDATA.[dataInt])) ELSE CONVERT(NVARCHAR, 
                        MEMBDATA.[dataInt]) END ELSE NULL END, '') AS MemberData
  FROM         (SELECT     id, text
                         FROM          dbo.umbracoNode
                         WHERE      (nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')) AS MEMBTYPEID LEFT OUTER JOIN
                            (SELECT     nodeId, contentType
                              FROM          dbo.cmsContent) AS MEMBLST ON MEMBLST.contentType = MEMBTYPEID.id LEFT OUTER JOIN
                        dbo.cmsPropertyType AS MEMBTYPES ON MEMBTYPES.contentTypeId = MEMBLST.contentType LEFT OUTER JOIN
                        dbo.cmsPropertyData AS MEMBDATA ON MEMBDATA.contentNodeId = MEMBLST.nodeId AND 
                        MEMBDATA.propertytypeid = MEMBTYPES.id LEFT OUTER JOIN
                        dbo.cmsMember AS MEMB ON MEMB.nodeId = MEMBLST.nodeId
  WHERE     (MEMBLST.nodeId IS NOT NULL)
  </code></pre>
  
  <p>My SQL skills are fairly basic, so hope someone can help</p>
  
  <p>~~~~~~~~~~~~ WORKING CODE ~~~~~~~~~~~~~</p>
  
  <p>Managed to get it working and here is the code</p>
  
  <pre><code>SELECT MEMBTYPEID.text AS MemberType, MEMBLST.nodeId, MEMBTYPES.Name AS MemberField, MEMBTYPES.Alias AS MemberFieldAlias, MEMB.LoginName,
  ISNULL(CASE 
  WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Nvarchar') THEN MEMBDATA.[dataNvarchar] 
  WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Ntext') THEN MEMBDATA.[dataNtext] 
  WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Date') THEN CONVERT(NVARCHAR, MEMBDATA.[dataDate]) 
  WHEN MEMBTYPES.datatypeID IN (SELECT NodeId FROM DBO.CMSDATATYPE WHERE DBTYPE = 'Integer') THEN  CONVERT(NVARCHAR, MEMBDATA.[dataInt])
  ELSE NULL END, NULL) 
  AS MemberData 
  FROM 
  (SELECT id, text FROM dbo.umbracoNode WHERE (nodeObjectType = '9b5416fb-e72f-45a9-a07b-5a9a2709ce43')) AS MEMBTYPEID 
  LEFT OUTER JOIN (SELECT nodeId, contentType FROM dbo.cmsContent) AS MEMBLST ON MEMBLST.contentType = MEMBTYPEID.id 
  LEFT OUTER JOIN dbo.cmsPropertyType AS MEMBTYPES ON MEMBTYPES.contentTypeId = MEMBLST.contentType 
  LEFT OUTER JOIN dbo.cmsPropertyData AS MEMBDATA ON MEMBDATA.contentNodeId = MEMBLST.nodeId AND MEMBDATA.propertytypeid = MEMBTYPES.id 
  LEFT OUTER JOIN dbo.cmsMember AS MEMB ON MEMB.nodeId = MEMBLST.nodeId
  WHERE (MEMBLST.nodeId IS NOT NULL)
  </code></pre>