CODEWITHSUNDEEP
mysqlparent-childcommon-table-expressionrecursive-cte
Vijay Ananth
Vijay Ananth

Reputation: 1

Recursive way of finding parent and children for each row

I have a table with client_id and parent_clientid. Each client can be a parent and can contain multiple children. I would like to know, for each row, how to get the associated parent and children in a concatenated string (like in the example). I do not want to run the query based on client id or primary client id. The expected result string should list the presence of this clientid wherever it is present. Is it possible?

client_id primary_client_id Expected result
1 NULL 1,10,11,57,65
2 NULL 2,3,4,6,56
3 2 2,4,6,56
4 2 2,3,6,56
6 2 2,3,4,56
7 NULL 7,8,9,58,59
8 7 7,9,58,59
9 7 7,8,58,59
10 1 1,11,57,65
11 1 1,10,57,65
48 NULL 48,49,50,52
49 48 48,50,52
50 48 48,49,52
51 NULL 51
52 48 48,49,50
53 NULL 53
54 NULL 54
55 NULL 55
56 2 2,3,4,6
57 1 1,10,11,65
58 7 7,8,9,59
59 7 7,8,9,58
60 NULL 60
61 NULL 61
62 NULL 62
63 NULL 63
64 NULL 64
65 1 1,10,11,57,65

enter image description here

with recursive myCTE (root_id, client_id, firstname, lastname, primary_client_id) as (
    # the most basic level, we need to remember the root id and name
    select client_id as root_id,
           client_id,
           firstname,
           lastname,
           primary_client_id
    from clients
    union all
    select mC.root_id,
           cl.client_id,
           mC.firstname,
           mC.lastname,
           cl.primary_client_id
    from clients cl
    inner join myCTE mC on cl.primary_client_id = mC.client_id
    
)
select * from (
select root_id, group_concat(client_id order by client_id ASC) as simple_skus
from myCTE
group by root_id
) t1

Upvotes: 0

Views: 57

Answers (1)

Dylan M. Loszak
Dylan M. Loszak

Reputation: 34

Here's the thing, without a starting point like a client_id, crafting a query to fetch the chains for every record in the table will likely be a performance nightmare. The closest I can think of is to get all the possible 'family trees' and have those concatenated together. The catch here is you'd get each client listed out multiple times (in any/each group they belong to). Unfortunately, databases just aren't built to handle that kind of recursive relationship tracing in a super-efficient way.

Upvotes: 0

Related Questions