SQL Recursive query over same table

Question

I am new in SQL and I am trying to do a recursive query over the same table to find the brokers of the "master brokers" I have a table that looks like this (it can grow to any amount of rows and deepness)

So I need a result like this:

master_id => broker_id

I have checked about how to do it and I got:

WITH admin_has_master_brokers
AS (
    SELECT DISTINCT master_broker_id, admin_id
        FROM admin_has_master_brokers
            
    UNION ALL
    /*I DO NOT KNOW HOW TO DO THIS SECTION*/
    SELECT    
        master_broker_id, admin_id
    FROM admin_has_master_brokers
)
SELECT 
    *
FROM 
    admin_has_master_brokers
ORDER BY master_broker_id ASC

But I can not understand how to do the recursive part to only get the results I need because I am getting this:

Any idea?

What do you mean by: "I DO NOT KNOW HOW TO DO THIS SECTION"? What result are you getting from the query compared to your expectation? Is it not the same? — Alexander Falk
– Alexander Falk, Commented Jul 15, 2021 at 6:49
@AlexanderFalk hey mate I have added the result I am getting — Tomas Lucena
– Tomas Lucena, Commented Jul 15, 2021 at 7:18

Serg · Accepted Answer · 2021-07-15 08:53:14Z

2

Povided original table is Mytable the query lists all desendants of every master_broker_id.

WITH RECURSIVE admin_has_master_brokers
AS (
    SELECT DISTINCT master_broker_id master, master_broker_id, admin_id
    FROM mytable
       
    UNION ALL

    SELECT a.master,  
        m.master_broker_id, m.admin_id
    FROM admin_has_master_brokers a
    JOIN mytable m ON m.master_broker_id = a.admin_id
)
SELECT DISTINCT master, admin_id    
FROM 
    admin_has_master_brokers
ORDER BY master, admin_id

edited Jul 15, 2021 at 8:53

answered Jul 15, 2021 at 7:33

Serg

22.9k5 gold badges23 silver badges50 bronze badges

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

2 Comments

FanoFN Over a year ago

Nice, but the query is missing the word RECURSIVE

Serg Over a year ago

@FaNo_FN, thank you. Missed mariadb tag, corrected.

Collectives™ on Stack Overflow

SQL Recursive query over same table

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related