First of all thank you for replying! It's close, but not quite. As I said, I need to get the entire chain Car -> CarPart -> Car -> CarPart, etc. The query you wrote gets the cars and their parts, which are directly related to the @StartCar and its parts. However, it doesn't get the rest of the chain. If I were to revise my setup code to this:

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)

As you can see, I added "Hugo", which will not get returned because it's not related to the @StartCar "Chevy" or its parts.

I still think it has to be some sort of recursive CTE, but I can't figure out how to stop it and prevent infinite loop.