IF OBJECT_ID('Car') IS NOT NULL DROP TABLE Car
CREATE TABLE Car (
	CarID INT,
	CarName VARCHAR(16)
	)

IF OBJECT_ID('CarPart') IS NOT NULL DROP TABLE CarPart
CREATE TABLE CarPart (
	PartID INT,
	PartName VARCHAR(16),
	CarID INT
    )
	
INSERT INTO Car
VALUES (1, 'Chevy'),
		(2, 'Ford'),
		(3, 'Toyota'),
		(4, 'Honda'),
		(5, 'Nissan')

INSERT INTO CarPart 
VALUES  (110, 'Engine', 1),
	  (120, 'Engine', 2),
	  (210, 'Door', 1),
	  (220, 'Door', 3),
	  (310, 'Seat', 4),
	  (320, 'Seat', 5),
	  (410, 'Window', 3),
	  (510, 'Wheel', 2)

DECLARE @StartCar VARCHAR(16) = 'Chevy'

;WITH cte (CarName, PartName)
AS
(
	SELECT c.CarName, cp.PartName
	FROM CarPart cp
	JOIN Car c
	ON   cp.CarID = c.CarID
	WHERE c.CarName = @StartCar
	UNION ALL
	SELECT c.CarName, cp.PartName
	FROM CarPart cp
	JOIN Car c
	ON   cp.CarID = c.CarID
	JOIN cte cte
	ON	cp.PartName = cte.PartName
)
SELECT CarName, PartName
FROM cte

;with originalCar AS (
	SELECT CarName, PartName
	FROM Car c INNER JOIN CarPart cp ON c.CarID = cp.CarID
	WHERE CarName = @StartCar
	),
	otherCars AS (
	select carName, partName
	FROM Car c INNER JOIN CarPart cp ON c.CarID = cp.CarID
	WHERE carName in (
		SELECT carName
		FROM Car c INNER JOIN CarPart cp ON c.CarID = cp.CarID
		WHERE PartName in (SELECT PartName FROM originalCar)
			and CarName != @startCar
		)	
	)
select carname, partname
from originalCar
union all
select carname, partname
from otherCars
order by CarName

IF OBJECT_ID('Car') IS NOT NULL DROP TABLE Car
CREATE TABLE Car (
	CarID INT,
	CarName VARCHAR(16)
	)

IF OBJECT_ID('CarPart') IS NOT NULL DROP TABLE CarPart
CREATE TABLE CarPart (
	PartID INT,
	PartName VARCHAR(16),
	CarID INT
    )
	
INSERT INTO Car
VALUES (1, 'Chevy'),
		(2, 'Ford'),
		(3, 'Toyota'),
		(4, 'Honda'),
		(5, 'Nissan'),
		(6, 'Hugo')

INSERT INTO CarPart 
VALUES  (110, 'Engine', 1),
	  (120, 'Engine', 2),
	  (210, 'Door', 1),
	  (220, 'Door', 3),
	  (310, 'Seat', 4),
	  (320, 'Seat', 5),
	  (410, 'Window', 3),
	  (510, 'Wheel', 2),
	  (420, 'Window', 6)

IF OBJECT_ID('Car') IS NOT NULL DROP TABLE Car;
CREATE TABLE Car (
	CarID INT,
	CarName VARCHAR(16)
	);

IF OBJECT_ID('CarPart') IS NOT NULL DROP TABLE CarPart;
CREATE TABLE CarPart (
	PartID INT,
	PartName VARCHAR(16),
	CarID INT
    );
	
INSERT INTO Car
VALUES (1, 'Chevy'),
		(2, 'Ford'),
		(3, 'Toyota'),
		(4, 'Honda'),
		(5, 'Nissan'),
		(6, 'Hugo');

INSERT INTO CarPart 
VALUES  (110, 'Engine', 1),
	  (120, 'Engine', 2),
	  (210, 'Door', 1),
	  (220, 'Door', 3),
	  (310, 'Seat', 4),
	  (320, 'Seat', 5),
	  (410, 'Window', 3),
	  (510, 'Wheel', 2),
	  (420, 'Window', 6);

select
    c.CarName,
    cp.PartName
from
    dbo.Car c
    inner join dbo.CarPart cp
        on (c.CarID = cp.CarID)
order by
    c.CarName;

IF OBJECT_ID('Car') IS NOT NULL DROP TABLE Car;
IF OBJECT_ID('CarPart') IS NOT NULL DROP TABLE CarPart;

WITH cte(CarID,hier) AS (
  SELECT CarID, CAST('/'+LTRIM(CarID)+'/' AS varchar(max))
  FROM Car
  WHERE CarName = @StartCar

  UNION ALL

  SELECT c2.CarID, hier+LTRIM(c2.CarID)+'/'
  FROM Car AS c
  JOIN cte ON cte.CarID = c.CarID
  JOIN CarPart AS c1 ON c.CarID = c1.CarID
  JOIN CarPart AS c2 ON c2.PartName = c1.PartName
  WHERE hier NOT LIKE '%/'+LTRIM(c2.CarID)+'/%'
)

SELECT
  c.CarName, cp.PartName
FROM Car AS c
JOIN CarPart AS cp ON cp.CarID = c.CarID
JOIN cte on cte.CarID = c.CarID