CODEWITHSUNDEEP
mysqlsqlrecursive-query
Deepak soni
Deepak soni

Reputation: 21

Recursive query in MySQL( find direct and indirect supervisor of Bob )?

There is one problem that I encountered and that can we solved using recursive query but my code is giving error in MySQL.

Problem is as follow:

person    supervisor
Mary       Susan
David      Mary
Bob        Alice
Alice      David

Find all supervisor(direct and indirect) of Bob?

Here is my solution for above question:

create recursive view find_all(person,supervisor,depth) as
(
        select person,supervisor,0 as depth from employee as e
        where e.person = 'Bob'
        union all
        select in1.person,out1.supervisor,in1.depth+1
        from find_all as in1,employee as out1
        and in1.supervisor = out1.person
        and in1.depth<=100
        
);

And for output:

select * from find_all;

Upvotes: 0

Views: 444

Answers (1)

Jon Armstrong
Jon Armstrong

Reputation: 4694

Here's one approach. Just create a normal view. The view contains the recursive WITH clause.

CREATE VIEW find_all(person,supervisor,depth) AS
WITH RECURSIVE cte1 AS (
        SELECT person, supervisor, 0 AS depth
          FROM employee AS e
         WHERE e.person = 'Bob'
         UNION ALL
        SELECT in1.person, out1.supervisor, in1.depth+1
          FROM cte1 AS in1, employee AS out1
         WHERE in1.supervisor = out1.person
           AND in1.depth <= 100
     )
SELECT * FROM cte1
;

Result:

select * from find_all;

+--------+------------+-------+
| person | supervisor | depth |
+--------+------------+-------+
| Bob    | Alice      |     0 |
| Bob    | David      |     1 |
| Bob    | Mary       |     2 |
| Bob    | Susan      |     3 |
+--------+------------+-------+

Alternate form:

The <from clause> contains a <table reference list> which is a comma separated list of <table reference>, <table reference>, .... This produces cross join behavior. We then use a WHERE clause to effectively inner join these table references.

It's commonly suggested to use a single <table reference>, for several good reasons, which can contain a <joined table>. The second UNION term below contains an example of a <joined table>.

A couple of advantages are ease of identifying (and not accidentally forgetting) the corresponding <join condition> and easier to change between join types.

CREATE VIEW find_all(person,supervisor,depth) AS
WITH RECURSIVE cte1 AS (
        SELECT person, supervisor, 0 AS depth
          FROM employee AS e
         WHERE e.person = 'Bob'
         UNION ALL
        SELECT in1.person, out1.supervisor, in1.depth+1
          FROM cte1     AS in1
          JOIN employee AS out1
            ON in1.supervisor = out1.person
         WHERE in1.depth <= 100
     )
SELECT * FROM cte1
;

In this case, the depth logic is not really part of the <join condition>. It's really a subsequent filter of the rows produced by the join.

The difference is seen when, for example, we have a left outer join. We don't want that logic to just generate more nulls for the right table columns. We want that logic to produce no further rows when depth is exceeded.

Upvotes: 1

Related Questions