Mysql 8 recursive query with two mandatory parents

Question

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');

@MiguelAngel_LV - Please add same input data of all the tables you want to query on and output of them accordingly. — Ali Sajid
– Ali Sajid, Commented Apr 26, 2024 at 19:14
Add sample data as CREATE TABLE + INSERT INTO. See Tips for asking a good Structured Query Language (SQL) question, #5. — Akina
– Akina, Commented Apr 26, 2024 at 20:04

Akina · Accepted Answer · 2024-04-27 06:20:34Z

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)

answered Apr 27, 2024 at 6:20

Akina

43.2k6 gold badges17 silver badges30 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

ValNik · Accepted Answer · 2024-04-27 16:29:52Z

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.

Collectives™ on Stack Overflow

Mysql 8 recursive query with two mandatory parents

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related