Question 383695 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql 8 recursive query with two mandatory parents

I am working with a hierarchy problem where the nodes have two parents and need get only the nodes where both parents are in the result.

In the happy world, this query would be perfect

 WITH RECURSIVE granters (id, parents) AS (
    SELECT id FROM `hierarchy` WHERE id in ("id1", "id2", "id6"....)
    UNION ALL
    SELECT h1.id, h1.parents FROM `hierarchy` h1 
     WHERE h1.parent2 in (SELECT id FROM granters)
     AND h1.parent2 in (SELECT id FROM granters)
)
SELECT * FROM granters g

But mysql don’t allow use CTE as subquery, in multiples inner joins or in right join, so, I don’t know how filter it.

My best approach is this:

 WITH RECURSIVE granters (id) AS (
    SELECT id FROM `hierarchy` WHERE id in ("A", "B")
    UNION ALL
    SELECT h1.id FROM `hierarchy` h1 INNER JOIN granters g 
        ON h1.parent1 = g.id
        OR h1.parent2 = g.id
)
SELECT * FROM hierarchy g 
WHERE g.id IN ("A", "B")
OR (
   g.parent1 IN (SELECT * FROM granters)
   AND g.parent2 IN (SELECT * FROM granters)
)

But the problem is that first return all element with as least one parent and after filter all elements don’t have all parents… but if a grandfather is missing return the node anyway because both parents are in granters.

Data

id parent1 parent2
A A1 A2
B B1 B2
C A B
D B H
E C D

Actual output:

ID parent1 parent2 notes
A A1 A2 Good, is a selected value
B B1 B2 Good, is selected value
C A B Good, A and B are in result
E C D Bad, D is missing because H is missing

Expected output:

ID parent1 parent2
A A1 A2
B B1 B2
C A B 

-- Data

CREATE TABLE `hierarchy` (
  `id` char(36) NOT NULL,
  `parent1` char(36),
  `parent2` char(36),
  PRIMARY KEY (`id`)
);

INSERT INTO `hierarchy` (id,parent1,parent2) VALUES
     ('A','A1','A2'),
     ('B','B1','B2'),
     ('C','A','B'),
     ('D','B','H'),
     ('E','C','D');
Tags:

2

Answers


  1. 0 
    SET @ids = 'A,B';   -- set initial components list

WITH RECURSIVE
cte AS (
  SELECT *, @ids
  FROM hierarchy
  WHERE FIND_IN_SET(id, @ids)
  UNION
  SELECT hierarchy.*, @ids:=CONCAT_WS(',', @ids, id)
  FROM hierarchy
  WHERE FIND_IN_SET(parent1, @ids) AND FIND_IN_SET(parent2, @ids)
)
SELECT id, parent1, parent2
FROM cte

    fiddle (source data is expanded)

    Login or Signup to reply.
  2. 0

    MySQL allow several join’s in recursive query, excluding the request itself.
    We try replace parent by parent of parent up to values in(‘A’,’B’),
    then we select the appropriate rows.

    Example

    with recursive  r as (
  select 0 lvl,id,parent1 p1 ,parent2 p2
    ,parent1,parent2
  from hierarchy
  union all
  select lvl+1,r.id
    ,case when r.p1 not in ('A','B') then h1.parent1
     else r.p1
     end p1
    ,case when r.p2 not in ('A','B') then h2.parent2
     else r.p2
     end p2
    ,r.parent1,r.parent2
  from r 
  inner join hierarchy h1 on h1.id=r.p1
  inner join hierarchy h2 on h2.id=r.p2
  where (r.p1 not in ('A','B') or r.p2 not in ('A','B')) 
)
select id,parent1,parent2 from r 
where (p1 in ('A','B') and p2 in ('A','B'))
  or (id in ('A','B'))
order by id,lvl;

    Output is

    id parent1 parent2
    A A1 A2
    B B1 B2
    C A B
    F B C
    I F C

    With test data

    CREATE TABLE hierarchy (id char(36) NOT NULL, parent1 char(36), parent2 char(36), PRIMARY KEY (id));
INSERT INTO hierarchy (id,parent1,parent2) VALUES
     ('A','A1','A2'), -- Good, is a selected value
     ('B','B1','B2'), -- Good, is selected value
     ('C','A','B'),   -- Good, A and B are in result
     ('D','B','H'),   -- Bad, D is missing because H is missing
     ('E','C','D'),   -- Bad, E is missing because D is missing
     ('F','B','C'),   -- Good, B,C in result
     ('K','L','A'),   -- Bad, L not in result
     ('H','L','M'),   -- Bad, L,M not in result
     ('I','F','C'),   -- Good, F,C in result
     ('J','K','C')    -- Bad, K not in result
  ;

    Demo

    Upd1. Perhaps, since we are not checking all the options, additional verification is needed. Need to think.

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search