Menu
I have a table with following structure
Table name: matches
That basically stores which product is matching which product. I need to process this table And store in a groups table like below.
Table Name: groups
group_ID stores the MIN Product_ID of the Product_IDS that form a group. To give an example let's say
If A is matching B and B is Matching C then three rows should go to group table in format (A, A), (A, B), (A, C)
I have tried looking into co-related subqueries and CTE, but not getting this to implement.
I need to do this all in SQL.
Thanks for the help .
596
Recursion occurs because of the query referencing the CTE itself based on the Employee in the Managers CTE as input. The join then returns the employees who have their managers as the previous record returned by the recursive query. The recursive query is repeated until it returns an empty result set.
First, execute the anchor member to form the base result set (R0), use this result for the next iteration. Second, execute the recursive member with the input result set from the previous iteration (Ri-1) and return a sub-result set (Ri) until the termination condition is met.
A recursive CTE references itself. It returns the result subset, then it repeatedly (recursively) references itself, and stops when it returns all the results. FROM cte_name; Again, at the beginning of your CTE is the WITH clause.
Try this:
;WITH CTE AS ( SELECT DISTINCT M1.Product_ID Group_ID, M1.Product_ID FROM matches M1 LEFT JOIN matches M2 ON M1.Product_Id = M2.matching_Product_Id WHERE M2.matching_Product_Id IS NULL UNION ALL SELECT C.Group_ID, M.matching_Product_Id FROM CTE C JOIN matches M ON C.Product_ID = M.Product_ID ) SELECT * FROM CTE ORDER BY Group_ID You can use OPTION(MAXRECURSION n) to control recursion depth.
SQL FIDDLE DEMO
154
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
