CTE statement sql server
WITH PersonCTE (BusinessEntityID, FirstName, LastName)
AS (SELECT Person.BusinessEntityID,
FirstName,
LastName
FROM Person.Person
WHERE LastName LIKE 'C%'),
PhoneCTE (BusinessEntityID, PhoneNumber)
AS (SELECT BusinessEntityID,
PhoneNumber
FROM Person.PersonPhone)
SELECT FirstName,
LastName,
PhoneNumber
FROM PersonCTE
INNER JOIN
PhoneCTE
ON PersonCTE.BusinessEntityID = PhoneCTE.BusinessEntityID;