Recursive query in SQL Server

recursive query in sql server without cte
recursive queries in sql w3schools
recursive loop in sql server
sql recursive query parent child
sql recursive query employee manager
recursive sql query mysql
recursive cte hierarchy
oracle sql recursive query

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 .

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

Recursive CTEs Explained, Tou can use recursive CTEs to solve problems other queries cannot. the Microsoft SQL Server Management Studio and the AdventureWorks2012 database. Recursive query in SQL Server. Ask Question Asked 7 years, 6 months ago. Active 1 year, 4 months ago. Viewed 100k times 31. 9. I have a table with following structure

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;

Simplifying Recursive SQL Queries, How can we write pure SQL queries that operate on hierarchical, self-referential tables? Recursive Common Table Expressions can be a great� The recursive CTE, Managers, defines an initialization query and a recursive execution query The initialization query returns the base result and is the highest level in the hierarchy. This is identified by the The recursive execution query is then joined to the initialization query using the

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

Recursive queries, Recursive queries are used to query hierarchical data. The SQL standard defines a special syntax for common table expressions to enable recursive processing. Sql Server: CTE Recursive Query to Get Employee Manager Hierarchy with Level; How to join first,middle and last name in Sql Server | Concatenate strings together in Sql Server; How to create Login page/form and check username,password in asp.net using stored procedure and sql server database; SQL Server: How to remove First and Last character

An Introduction to Recursive SQL, DB2, Microsoft SQL Server, Oracle and PostgreSQL all support recursive queries using CTEs. Note that Oracle also offers an alternative syntax� SQL Server support graph table start from version 2017, I am wondering if graph query support recursive? below is the example: CREATE TABLE Person ( ID INTEGER PRIMARY KEY, name VARCHAR(100)) ;

WITH common_table_expression (Transact-SQL), Transact-SQL reference for how to use common table expressions (CTE) in The recursive CTE definition must contain at least two CTE query� The Recursive query (aliased as ‘Emp’) represents everyone at Levels 2 and 3. In order to visualize each level in a result set, you will need to add an expression field to each query. Add the expression “1 AS EmpLevel” to the Anchor query and the expression “2 AS EmpLevel” to the Recursive query.

Recursive Queries using Common Table Expressions (CTE) in SQL , In SQL Server 2000, you need to implement recursive queries to retrieve data which is presented in a hierarchical format. We normally resort to�

Comments
  • You can use a recursive CTE.
  • 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.
  • 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.
  • When tried with my actual data, this is not returning any result. http://sqlfiddle.com/#!3/aff16/1
  • @Ankit I think you mast avoid one of pairs like (1, 2) and (2, 1).
  • thanks I did what you said, see here 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?
  • OK, if you add (2294543, 1802989) you get infinite recursion with this pair (1802989, 2294543). See my comment above.
  • 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.
  • -It's not returning correct result http://sqlfiddle.com/#!3/aff16/6
  • 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