SELECT [Theme].[PK_Theme], [Theme].[Name], [ThemeType].[Type]
  FROM [Theme]
  LEFT OUTER JOIN [ThemeType]
    ON [Theme].[ThemeTypeId] = [ThemeType].[PK_ThemeType]
  JOIN [ProductTheme] ON [ProductTheme].[ThemeId]=[Theme].[PK_Theme]
 WHERE ProductTheme.ProductID LIKE '%'
   AND ProductTheme.ThemeId = Theme.PK_Theme
   AND COALESCE([THEME].[THEMETYPEID], 'null') LIKE '%'
 GROUP BY [Theme].[Name], [ThemeType].[Type], [Theme].[PK_Theme]
 ORDER BY CASE WHEN [ThemeType].[Type] IS NULL THEN 0 ELSE 1 END, [Theme].[Name]

COALESCE([THEME].[THEMETYPEID], 'null')

AND COALESCE([THEME].[THEMETYPEID], 'null') LIKE '%'

AND COALESCE(cast([THEME].[THEMETYPEID] as varchar(255)), 'null') LIKE '%'

AND [THEME].[THEMETYPEID] is not null

AND COALESCE([THEME].[THEMETYPEID], 'null') LIKE '%'

AND COALESCE([THEME].[THEMETYPEID], 0) = 0%