I have this table:
id | name | parent_id |
---|---|---|
1 | mike | 0 |
2 | jeff | 0 |
3 | bill | 2 |
4 | sara | 1 |
5 | sam | 4 |
6 | shai | 5 |
I want to find first parent name,id by send id OR parent_id :
For example:
If I Send 6 id(shai) I want To Show That The First Parent That Name Is mike(id:1) But My Query Only Shows Previous parent and When I send 6 As id It Only Show sam(id:5). How Can I Find First Parent By SQL Query Or Php Code?
This Is My Code:
SELECT child.id, child.name, child.parent_id, parent.name as ParentName FROM test child JOIN test parent ON child.parent_id = parent.id WHERE child.id=6;
CodePudding user response:
CREATE TABLE test (id INT, name VARCHAR(255), parent_id INT); INSERT INTO test VALUES (1, 'mike', 0), (2, 'jeff', 0), (3, 'bill', 2), (4, 'sara', 1), (5, 'sam', 4), (6, 'shai', 5); SELECT * FROM test;
id name parent_id 1 mike 0 2 jeff 0 3 bill 2 4 sara 1 5 sam 4 6 shai 5
CREATE FUNCTION get_most_parent (id INT) RETURNS CHAR(255) BEGIN DECLARE parent_name VARCHAR(255); REPEAT SELECT name, parent_id INTO parent_name, id FROM test WHERE test.id = id; UNTIL NOT id END REPEAT; RETURN parent_name; END
SELECT test.*, get_most_parent(id) TopParentName FROM test WHERE id IN (3, 6);
id name parent_id TopParentName 3 bill 2 jeff 6 shai 5 mike
db<>fiddle here
PS. Apply this method only to single user or tiny users set - the function executes the whole iterative selection process for each output row separately and independently. And avoid cycles in your data!