Recursive Query in SQL Server

Recursive query in SQL Server

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 query with parent-child relation

Just another option using the data type hierarchyid

There are some additional features and functions associated with hierarchyid

Example

-- Optional See 1st WHERE
Declare @Top int = null  --<<  Sets top of Hier Try 2

;with cteP as (
      Select ID
            ,parent_id 
            ,Name 
            ,HierID = convert(hierarchyid,concat('/',ID,'/'))
      From   YourTable 
      Where  IsNull(@Top,-1) = case when @Top is null then isnull(parent_id ,-1) else ID end
      --Where parent_id is null  -- Use this where if you always want the full hierarchy
      Union  All
      Select ID  = r.ID
            ,parent_id  = r.parent_id 
            ,Name   = r.Name
            ,HierID = convert(hierarchyid,concat(p.HierID.ToString(),r.ID,'/'))
      From   YourTable r
      Join   cteP p on r.parent_id  = p.ID)
Select Lvl   = HierID.GetLevel()
      ,ID
      ,parent_id
      ,Name  
 From cteP A
 Order By A.HierID

Results

Lvl ID  parent_id   Name
1   1   NULL        P1
1   2   NULL        P2
2   3   2           P2-1
2   4   2           P2-2
2   5   2           P2-3
3   6   5           P2-3-1
3   7   5           P2-3-2
1   8   NULL        P3
2   9   8           P3-1

Just for fun, If I set @Top to 2, the results would be

Lvl ID  parent_id   Name
1   2   NULL        P2
2   3   2           P2-1
2   4   2           P2-2
2   5   2           P2-3
3   6   5           P2-3-1
3   7   5           P2-3-2

Using SQL recursive query as AND statement

I think I've worked out what you want.

You need to recurse through all the nodes and their children, returning its state and its ultimate root parent_id.

Then aggregate by that ID and exclude any group that contains a row with state = 'not-legit'. In other words, flip the logic to a double negative.

WITH cte AS (
    SELECT rH.id, rH.state, rH.id AS top_parent
        FROM tbl_request as rH 
        WHERE (rH.state is null or rH.state <> 'not-legit')
          AND rH.parent_id IS NULL
    UNION ALL
    SELECT rH.id, rH.state, cte.top_parent
        FROM tbl_request as rH
        JOIN cte
          ON rH.parent_id = cte.id
)
SELECT top_parent
FROM cte
GROUP BY
    cte.top_parent
    HAVING COUNT(CASE WHEN cte.state = 'not-legit' THEN 1 END) = 0;

You could also change the logic back to a positive, but it would need to look like this:

HAVING COUNT(CASE WHEN cte.state is null or cte.state <> 'not-legit' THEN 1 END) = COUNT(*)

In other words, there are the same number of these filtered rows as there are all rows.

This feels more complex than what I have put above.

SQL Fiddle

SQL recursive query to get department code

Try something like this:

WITH Hierarchy AS
(
    -- create the "anchor" - the toplevel node(s)
    SELECT
        u.emp, u.manager, u.code, 0 AS Level
    FROM
        Users u
    WHERE
        u.manager IS NULL

    UNION ALL

    -- recursive part - join subordinate to manager, one level up
    SELECT
        u.emp, u.manager, COALESCE(u.code, h.code), h.Level + 1
    FROM
        Hierarchy h
    INNER JOIN
        Users u ON u.manager = h.emp
)
SELECT
    *
FROM
    Hierarchy

This results in a dataset like this:

emp     manager code    Level
-----------------------------
boss    NULL    ZZZ      0
subboss boss    AQQ      1
subsub  boss    SUBSUB   1
steve   subsub  NULL     2
rick    steve   NULL     3
john    subboss ABC      2
alan    subboss NULL     2

Recursive query execute on server but not in local

I don't know the reason about why this is happening to you...
What I can suggest is to follow these steps to achieve your goal:

1. Disable all your foreign key constraints with this: EXEC sp_MSforeachtable "ALTER TABLE ? NOCHECK CONSTRAINT all"
2. Then perform all the delete you want
3. Enable all your foreign key constraints with this: exec sp_MSforeachtable @command1="print '?'", @command2="ALTER TABLE ? WITH CHECK CHECK CONSTRAINT all"

You may argue that this is a risky cheat but let me explain:
You probabily have a pool of tables seen by the users and some system setting tables. These two groups of tables will not be linked together (if not even your recurive query will say to you that is impossible to delete a "user table" if you don't delete a system setting table).
So you can trust my logic and truncate all the "user table" with your eyes closed

SQL Server RETURN in order recursive query

It's hard to say exactly what you need here without proper sample data, but it seems if you concatenate each item's position with all its parents, you can order by that.

CREATE OR ALTER PROCEDURE GETBOM 
@TL VARCHAR(10)

AS

SET NOCOUNT ON;

WITH TESTBOM AS
(
    SELECT POSITION, PARENT, PART, QUANTITY, CAST(POSITION AS nvarchar(100)) AS Ordering
    FROM BOM
    WHERE [PART] = @TL
      and [Parent] is null

    UNION ALL

    SELECT E.[POSITION],E.[PARENT],E.[PART],E.[QUANTITY], CAST(CONCAT(M.Ordering, E.POSITION) AS nvarchar(200)) 
    FROM BOM E
    INNER JOIN TESTBOM M ON E.[PARENT]=M.[PART] 
)
SELECT
  POSITION, PARENT, PART, QUANTITY
FROM TESTBOM
ORDER BY
  Ordering;

---

Related Topics