I need list of all parent of a child node

Question

My SQL data

+----+------------+-------------+
| id | member_id  | refrence_id |
+----+------------+-------------+
|  1 | M3PL1234   | Admin       |
|  2 | M3PL368182 | M3PL1234    |
|  3 | M3PL532935 | M3PL368182  |
|  4 | M3PL846330 | M3PL1234    |
|  5 | M3PL611743 | M3PL368182  |
|  6 | M3PL157382 | M3PL611743  |
|  7 | M3PL590169 | M3PL532935  |
|  8 | M3PL687435 | M3PL532935  |
|  9 | M3PL318809 | M3PL846330  |
| 10 | M3PL334477 | M3PL846330  |
| 11 | M3PL481196 | M3PL611743  |
| 12 | M3PL297917 | M3PL157382  |
+----+------------+-------------+

SQL query I tried

select distinct( refrence_id ) from (select * from member_profile)
products_sorted, (select @pv :='M3PL611743') initialisation where 
length(@pv := concat(@pv, ',',member_id))
and find_in_set(refrence_id, @pv) ;

The result I got:

+-------------+
| refrence_id |
+-------------+
| M3PL1234    |
| M3PL368182  |
| M3PL611743  |
| M3PL532935  |
| M3PL846330  |
| M3PL157382  |
| M3PL590169  |
| M3PL687435  |
+-------------+

result should be.

M3PL1234
M3PL368182

i have a feeling that "hack" to have a recursive query only can work correctly on integers (numbers).. — Raymond Nijland, Sep 23 '18 at 12:52
When considering hierarchical data, we tend to think of parents having many children, not the other way around. — Strawberry, Sep 23 '18 at 12:55
Besides it looks like you understand it wrong https://stackoverflow.com/a/33737203/2548147 because it's missing a ORDER BY in the subquery — Raymond Nijland, Sep 23 '18 at 12:55

score 0 · Accepted Answer · answered Sep 23 '18 at 13:17

Consider the following:

Adjacency List Model:

DROP TABLE IF EXISTS my_table;

CREATE TABLE my_table
(member_id INT NOT NULL PRIMARY KEY
,parent INT NULL
);

INSERT INTO my_table VALUES
(234,NULL),
(182,234),
(935,182),
(330,234),
(743,182),
(382,743),
(169,935),
(435,935),
(809,330),
(477,330),
(196,743),
(917,382);


SELECT y.member_id parent
     , z.member_id granparent 
  FROM my_table x 
  LEFT 
  JOIN my_table y 
    ON y.member_id = x.parent 
  LEFT 
  JOIN my_table z 
    ON z.member_id = y.parent 
 WHERE x.member_id = 743;
+--------+-------------+
| parent | grandparent |
+--------+-------------+
|    182 |         234 |
+--------+-------------+

...or Nested Set Model:

DROP TABLE IF EXISTS my_nested_set;

CREATE TABLE my_nested_set
(member_id INT NOT NULL PRIMARY KEY
, lft INT NOT NULL
, rgt INT NOT NULL
);

INSERT INTO my_nested_set VALUES
(234      ,   1, 24),
(182      ,   2, 17),
(935      ,   3,  8),
(435      ,   4,  5),
(169      ,   6,  7),
(743      ,   9, 16),
(382      ,  10, 13),
(917      ,  11, 12),
(196      ,  14, 15),
(330      ,  18, 23),
(809      ,  19, 20),
(477      ,  21, 22);

SELECT x.member_id 
  FROM my_nested_set x 
  JOIN my_nested_set y 
    ON y.lft > x.lft 
   AND y.lft < x.rgt 
 WHERE y.member_id = 743;
+-----------+
| member_id |
+-----------+
|       182 |
|       234 |
+-----------+

Thanks, Guys, I have created a stored procedure to find result because I'm storing the result in another table or speed up my web application but I have never called a procedure by java code. Can u guide me ?? procedure name is 'list_parent_node('M3PL611743')' — Atul Arnav Sharma, Sep 24 '18 at 19:45

I need list of all parent of a child node

My SQL data

SQL query I tried

The result I got:

result should be.

1 Answers1