Recursive CTE to find all records SQL Server

Question

I am having below table structure. I want to write a recursive cte to get the bottom most table result. Highly appreciate your help.

CREATE TABLE Jobcard (
  jobcard_id INT NOT NULL PRIMARY KEY,
  jobcard_name varchar(20) NOT NULL,
);
CREATE TABLE Vehicle (
  vehicle_id INT NOT NULL PRIMARY KEY,
  vehicle_name varchar(20) NOT NULL
);

CREATE TABLE Jobacard_vehicle (
  jobcard_id INT NOT NULL,
  vehicle_id INT NOT NULL
);


INSERT INTO Jobcard (jobcard_id, jobcard_name) VALUES
(1, 'Job1'),(2, 'Job2'),(3, 'Job3'),
(4, 'Job4'),(5, 'Job5'),(6, 'Job6'),
(7, 'Job7'),(8, 'Job8'),(9, 'Job9');

INSERT INTO Vehicle (vehicle_id, vehicle_name) VALUES
(1, 'Vehicle1'),(2, 'Vehicle2'),(3, 'Vehicle3'),
(4, 'Vehicle4'),(5, 'Vehicle5'),(6, 'Vehicle6');

INSERT INTO Jobacard_vehicle (jobcard_id, vehicle_id) VALUES
(3, 1),(4, 2),(5, 3),
(9, 6),(7, 2),(5, 4),
(8, 4),(6, 1),(3, 5);

jobcard_id, vehicle_id
--------------------------
3           1
4           2
5           3
9           6
7           2
5           4
8           4
6           1
3           5

I want to get this result from Jobacard_vehicle table when I pass the vehicle id as 3 as 

vehicle id 3 is having jobcard 5 jobcard 5 is having vehicle 4 again vehicle 4 referred to jobcard 8 means all the jobcard refered by 3 or its counter part jobcards vehicles

jobcard_id, vehicle_id
--------------------------
5           3
5           4
8           4

Thank you.

Answer 1

Try to save full path and check it in the next recursion

DECLARE @startVehicleID int=3

;WITH vehCTE AS(
    SELECT jobcard_id,vehicle_id,CAST(CONCAT('(',jobcard_id,',',vehicle_id,')') AS varchar(MAX)) [path]
    FROM Jobacard_vehicle
    WHERE vehicle_id=@startVehicleID

    UNION ALL

    SELECT v.jobcard_id,v.vehicle_id,c.[path]+CONCAT('(',v.jobcard_id,',',v.vehicle_id,')')
    FROM Jobacard_vehicle v
    JOIN vehCTE c ON (v.jobcard_id=c.jobcard_id OR v.vehicle_id=c.vehicle_id) AND CHARINDEX(CONCAT('(',v.jobcard_id,',',v.vehicle_id,')'),c.[path])=0
)
SELECT *
FROM vehCTE
ORDER BY [path]

If you need to check Job->Vehicle->Job->Vehicle->... then I think you can use the following

DECLARE @startVehicleID int=3

;WITH vehCTE AS(
    SELECT
        jobcard_id,
        vehicle_id,
        CAST(CONCAT('(',jobcard_id,',',vehicle_id,')') AS varchar(MAX)) [path],
        1 NextIsJob
    FROM Jobacard_vehicle
    WHERE vehicle_id=@startVehicleID

    UNION ALL

    SELECT
        v.jobcard_id,
        v.vehicle_id,
        c.[path]+CONCAT('(',v.jobcard_id,',',v.vehicle_id,')'),
        IIF(c.NextIsJob=1,0,1)
    FROM Jobacard_vehicle v
    JOIN vehCTE c ON ((c.NextIsJob=1 AND v.jobcard_id=c.jobcard_id) OR (c.NextIsJob=0 AND v.vehicle_id=c.vehicle_id)) AND CHARINDEX(CONCAT('(',v.jobcard_id,',',v.vehicle_id,')'),c.[path])=0
)
SELECT *
FROM vehCTE
ORDER BY [path]