Recursive many-to-many CTE

Question

I am not a DB expert was trying to write cte for below scenario for sql server 2012 but could not resolve my problem. Appreciate if some one help me to figure this out.

I am having a many to many table called Jockcard2Item for for the tables Jobcard and items.

Jobcard may have multiple items and items can have many Jobcards,

in my case I want to find out all the Jobcards for given item id like bellow.

• jobcard1 having item 1, item 2.
• jobcard2 having item 2, item 3
• jobards3 having item 3
• jobcard4 having item 4

I want to get all jobcards associated to given item, if there other items which is refering the same jobcard (like jobcard2 referring item2) want to include that job cards too.

result should return jobcard1,jobcard3,jobcard3,

I was trying with below query which is running infinite.

DECLARE @itemId int
SELECT @itemId = 12

;WITH temp as(
    SELECT jobCard_ID, item_id FROM Jobcard2Item
    WHERE item_id = @EstimateID

    UNION ALL

    SELECT bi.jobCard_ID ,bi.item_id  FROM Jobcard2Item
    JOIN temp x ON bi.jobCard_ID= x.jobCard_ID where x.item_id  not IN (bi.item_id )
)

select * from temp option (maxrecursion 0)

sample date as bellow

id    jobcard_ID, item_ID
1     512         12
2     512         13
3     513         13
4     513         14
5     514         14
6     515         15
7     516         16

when I pass the 12 as item_ID it should return the result - 512,513,514,

Answer 1

This query should help:

select distinct A.jobcard_id from Jockcard2Item A join Jockcard2Item B
on A.jobcard_id = B.jobcard_id or A.item_id = B.item_id
where A.item_id <> B.item_id or A.jobcard_id <> B.jobcard_id

It doesn't require CTE (I tried, but I think it's impossible).