Common table expressions (the 'WITH' bit) go at the start of the query.

WITH TempTable AS (

SELECT ConstraintBasedGroupId, Constraints

FROM ConstraintBasedGroupBase

),

ResourceGroupToTeam AS (

SELECT

TempTable.ConstraintBasedGroupId,

SUBSTRING(item,CHARINDEX('{',item)+1,CHARINDEX('}',item)-CHARINDEX('{',item)-1) AS TeamId

FROM TempTable

CROSS APPLY

DelimitedSplit8K(REPLACE(Constraints,'||','|'),'|') split

)

SELECT ResourceGroupBase.Name AS ResourceGroupName,

TeamBase.Name AS TeamName

FROM ResourceGroupBase

INNER JOIN ResourceGroupToTeam

ON ResourceGroupBase.ResourceGroupId = ResourceGroupToTeam.ConstraintBasedGroupId

INNER JOIN TeamBase

ON ResourceGroupToTeam.TeamId = TeamBase.TeamId

____________________________________________________

Deja View - The strange feeling that somewhere, sometime you've optimised this query before

How to get the best help on a forum

http://www.sqlservercentral.com/articles/Best+Practices/61537