SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

level	id	ProductName	Parent	Child
1	1	carA	carA	Tire
1	2	carA	carA	meter
2	5	carA	meter	meterA
2	6	carA	meter	meterB
2	7	carA	meter	meterC
3	8	carA	meterC	meterCC
2	3	carA	Tire	tireA
2	4	carA	Tire	tireB

DECLARE @SearchTerm NVARCHAR(10) = N'carA';
DECLARE @Table AS TABLE ( id INT, product1 NVARCHAR(10), product2 NVARCHAR(10));

INSERT INTO @Table
VALUES ( 1, 'carA', 'Tire' )
,      ( 2, 'carA', 'meter' )
,      ( 3, 'Tire', 'tireA' )
,      ( 4, 'Tire', 'tireB' )
,      ( 5, 'meter', 'meterA' )
,      ( 6, 'meter', 'meterB' )
,      ( 7, 'meter', 'meterC' )
,      ( 8, 'meterC', 'meterCC' );

WITH cte AS ( SELECT    1           AS level
              ,         T1.id
              ,@SearchTerm as ProductName
              ,         T1.product1 Parent
              ,         T1.product2 Child
                FROM    @Table T1
               WHERE T1.product1 = @SearchTerm
              UNION ALL
              SELECT    cte.level + 1
              ,         T2.id
              ,@SearchTerm as ProductName
              ,         T2.product1 Parent
              ,         T2.product2 Child
                FROM    @Table T2
                JOIN cte ON cte.Child = T2.product1 )
                
SELECT  *  FROM  cte ;

level	id	ProductName	Parent	Child
1	1	carA	carA	Tire
1	2	carA	carA	meter
2	5	carA	meter	meterA
2	6	carA	meter	meterB
2	7	carA	meter	meterC
3	8	carA	meterC	meterCC
2	3	carA	Tire	tireA
2	4	carA	Tire	tireB