Recursive query in SQL Server

Question

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 .

You can use a [recursive CTE](http://msdn.microsoft.com/en-us/library/ms186243(v=sql.105).aspx). — Tim Schmelter, Jan 25 '13 at 08:35
Recursive CTE seems like only way to achieve recursive processing in SQL, but I am not really getting it right for my problem. Can you please give any example that is near to my requirement. Thanks for help. — Ankit, Jan 25 '13 at 08:40
Not Really, I am just going through all products, performing a matching algorithm and then saving the result of the matching in "matches" table. There is nothing like root node I think. — Ankit, Jan 25 '13 at 08:47

Hamlet Hakobyan · Accepted Answer · 2013-01-25T08:55:34.410

71

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

edited Jan 25 '13 at 08:55

answered Jan 25 '13 at 08:48

Hamlet Hakobyan

32,965
6
52
68

When tried with my actual data, this is not returning any result. [http://sqlfiddle.com/#!3/aff16/1](http://sqlfiddle.com/#!3/aff16/1) – Ankit Jan 25 '13 at 10:03
1

@Ankit I think you mast avoid one of pairs like (1, 2) and (2, 1). – Hamlet Hakobyan Jan 25 '13 at 10:12
thanks I did what you said, see here http://sqlfiddle.com/#!3/d41d8/8042 query with this data is working fine. but adding (2294543, 1802989) to the last breaks it and it start giving maximum recursion level reached error. Any idea why is this? what kind of data can cause this? – Ankit Jan 25 '13 at 12:33
OK, if you add (2294543, 1802989) you get infinite recursion with this pair (1802989, 2294543). See my comment above. – Hamlet Hakobyan Jan 29 '13 at 06:35
ok I got what is getting wrong. if data is like A matches B and C And C matches D then it works and made group (A, B, C, D) but if data is like A matches B and C AND D matches C then it made two groups (ABC) (DC). Seems like it works only one way not both. – Ankit Jan 30 '13 at 07:15
I get an error stating `String index out of range: 33` when I try to run the sql fiddle demo from the link. – Ryan Gates Jun 19 '15 at 18:21

score 2 · Answer 2 · answered Jan 25 '13 at 08:48

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;

-It's not returning correct result [http://sqlfiddle.com/#!3/aff16/6](http://sqlfiddle.com/#!3/aff16/6) — Ankit, Jan 25 '13 at 10:12

score 2 · Answer 3 · edited Mar 19 '19 at 19:36

2

Sample of the Recursive Level:

DECLARE @VALUE_CODE AS VARCHAR(5);

--SET @VALUE_CODE = 'A' -- Specify a level

WITH ViewValue AS
(
    SELECT ValueCode
    , ValueDesc
    , PrecedingValueCode
    FROM ValuesTable
    WHERE PrecedingValueCode IS NULL
    UNION ALL
    SELECT A.ValueCode
    , A.ValueDesc
    , A.PrecedingValueCode 
    FROM ValuesTable A
    INNER JOIN ViewValue V ON
        V.ValueCode = A.PrecedingValueCode
)

SELECT ValueCode, ValueDesc, PrecedingValueCode

FROM ViewValue

--WHERE PrecedingValueCode  = @VALUE_CODE -- Specific level

--WHERE PrecedingValueCode  IS NULL -- Root

edited Mar 19 '19 at 19:36

amanb

5,276
3
19
38

answered Mar 19 '19 at 18:18

Wander César Lourenço

31
2

1

Why do you declare @VALUE_CODE if every instance of it is commented out afterwards? Also, this answer seems very specific to your table format and not to the schema referenced in the question – PausePause Mar 19 '19 at 18:20

Recursive query in SQL Server

3 Answers3

Linked

Related