SELECT
    [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 '%[0-9]%'
ORDER BY
    CASE WHEN [THEMETYPE].[TYPE] IS NULL
         THEN 0
         ELSE 1
    END,
    [THEMETYPE].[TYPE]

WHERE ProductTheme.ProductID = 1

WHERE ProductTheme.ProductID LIKE '%'

SELECT DISTINCT [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 '%[0-9]%'
ORDER  BY CASE WHEN [THEMETYPE].[TYPE] IS NULL
THEN 0
ELSE 1
END,
[THEMETYPE].[TYPE]