--Declare necessary variables 
DECLARE @searchstring VARCHAR(20) 

--Set values for search 
SELECT @searchstring = 'SQL'

--Perform Search 
SELECT DISTINCT P.ID, P.Item 
FROM ##ProductParent P INNER JOIN ##ProductChild C ON P.ID = C.FKID 
WHERE 
   C.ChildDescription LIKE @searchstring + '%'
   OR 
   C.ChildDescription LIKE '% ' + @searchstring + '%'
ORDER BY P.ID 

DROP TABLE ##ProductChild 
DROP TABLE ##ProductParent