>>>>I must be missing something. I only see two records in the phone table (1 and 4) and that matches your final result. What should you be getting in your final resultset?
>>>
>>>All the numbers should be returned in one row.
>>
>>Please see my correction of your query in the reply to Tracy.
>
>Ok, that worked. One last thing on this....
>
>Here is my query to pull contacts for the detail report:
>
>

>SELECT	c.RecordId, c.LastName, c.FirstName, c.Family, c.Title, c.CompanyName, c.BirthDate,
>		a.Street1, a.Street2, a.City, a.State, a.ZipCode,
>		e.EmailAddr,
>		p.PhoneNum,
>		w.WebSiteURL,
>		cat.Caption AS Category,
>		sc.Caption AS Subcategory,
>		ccc.Caption AS CCCategory
>		FROM Contacts c

>		LEFT JOIN Phones p ON p.ContactId = c.RecordId AND p.IsPrimary = 1
>		LEFT JOIN Emails e ON e.ContactId = c.RecordId AND e.IsPrimary = 1
>		LEFT JOIN WebSites w ON w.ContactId = c.RecordId AND w.IsPrimary = 1
>		LEFT JOIN Categories cat ON cat.RecordId = c.CategoryId
>		LEFT JOIN Subcategories sc ON sc.RecordId = c.SubcategoryId
>		LEFT JOIN CCCategories ccc ON ccc.RecordId = c.CCCategoryId
>		WHERE c.FirstName LIKE '%' + @FirstName + '%' OR
>			  c.LastName LIKE '%' + @LastName + '%' OR
>			  c.Family LIKE '%' + @Family + '%' OR
>			  c.Title LIKE '%' + @Title + '%' OR
>			  c.CompanyName LIKE '%' + @CompanyName + '%' 
 OR
>			  cat.Caption LIKE '%' + @Category + '%' OR
>			  sc.Caption LIKE '%' + @Subcategory + '%'
>		ORDER BY ContactName, Title, CompanyName

>
>How do I include this so that the phone record values are on each contact row:
>
>

>SELECT ContactID, [1] AS [Home], [2] AS Office, [3] AS Fax, [4] AS Cell
>	FROM (SELECT ContactID, PhoneNum, PhoneTypeID FROM 
>			Phones WHERE
>			ContactID = @ContactId) src 
>	PIVOT (MIN(PhoneNum) for PhoneTypeID in ([1],[2],[3],[4])) pvt
>
>

Try

;with cte_Contacts as (SELECT c.RecordId, c.LastName, 
                c.FirstName, c.Family, c.Title, c.CompanyName, c.BirthDate,
		a.Street1, a.Street2, a.City, a.State, a.ZipCode,
		e.EmailAddr,
		w.WebSiteURL,
		cat.Caption AS Category,
		sc.Caption AS Subcategory,
		ccc.Caption AS CCCategory
		FROM Contacts c
		
                LEFT JOIN Addresses a ON a.ContactId = c.RecordId AND a.IsPrimary = 1
                and (a.Street1 LIKE '%' + @Street1 + '%' OR
			  a.Street2 LIKE '%' + @Street2 + '%' OR
			  a.City LIKE '%' + @City + '%' OR
			  a.State LIKE '%' + @State + '%'  OR  a.ZipCode LIKE '%' + @ZipCode + '%')
		LEFT JOIN Emails e ON e.ContactId = c.RecordId AND e.IsPrimary = 1
		LEFT JOIN WebSites w ON w.ContactId = c.RecordId AND w.IsPrimary = 1
		LEFT JOIN Categories cat ON cat.RecordId = c.CategoryId  AND cat.Caption LIKE '%' + @Category 
		LEFT JOIN Subcategories sc ON sc.RecordId = c.SubcategoryId 
                                   AND sc.Caption LIKE '%' + @Subcategory + '%'
		LEFT JOIN CCCategories ccc ON ccc.RecordId = c.CCCategoryId
		WHERE c.FirstName LIKE '%' + @FirstName + '%' OR
			  c.LastName LIKE '%' + @LastName + '%' OR
			  c.Family LIKE '%' + @Family + '%' OR
			  c.Title LIKE '%' + @Title + '%' OR
			  c.CompanyName LIKE '%' + @CompanyName + '%' ),
cte_AllPhones as (SELECT ContactID, [1] AS [Home], [2] AS Office, [3] AS Fax, [4] AS Cell
	FROM (SELECT ContactID, PhoneNum, PhoneTypeID FROM 
			Phones WHERE
			ContactID IN (select RecordID from cte_Contacts)) src 
	PIVOT (MIN(PhoneNum) for PhoneTypeID in ([1],[2],[3],[4])) pvt)

select c1.*, c2.[Home], c2.[Office], etc. from cte_Contacts c1 
LEFT JOIN cte_AllPhones c2 on c1.RecordID = c2.ContactID

Note, that I also moved criteria for address and categories into JOIN condition.

Here is a blog explaining why did I do this:
http://beyondrelational.com/blogs/naomi/archive/2010/03/22/why-left-join-doesn-t-bring-all-records-from-the-left-table.aspx