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 Addresses a ON a.ContactId = c.RecordId AND a.IsPrimary = 1 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 a.Street1 LIKE '%' + @Street1 + '%' OR a.Street2 LIKE '%' + @Street2 + '%' OR a.City LIKE '%' + @City + '%' OR a.State LIKE '%' + @State + '%' OR a.ZipCode LIKE '%' + @ZipCode + '%' OR cat.Caption LIKE '%' + @Category + '%' OR sc.Caption LIKE '%' + @Subcategory + '%' ORDER BY ContactName, Title, CompanyNameHow 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