Recursive query in SQL Server

34

I have a table with following structure

Table name: matches

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

enter image description here

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 .

This question is tagged with sql sql-server

~ Asked on 2013-01-25 08:34:12

The Best Answer is


62

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

~ Answered on 2013-01-25 08:48:57


2

Something like this (not tested)

with match_groups as (

  select product_id, 
         matching_product_id,
         product_id as group_id
  from matches
  where product_id not in (select matching_product_id from matches)

  union all

  select m.product_id, m.matching_product_id, p.group_id
  from matches m
    join match_groups p on m.product_id = p.matching_product_id
)
select group_id, product_id
from match_groups
order by group_id;

~ Answered on 2013-01-25 08:48:44


Most Viewed Questions: