Hi
Does this do what you require?
;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