Simple SQL path query?
postgresql, sql
Solution
On Postgres you can use a RECURSIVE query:
WITH RECURSIVE Rec as
(
SELECT id, parent_id, Name
FROM Hierarchy
WHERE Name = 'Sirenia'
UNION ALL
SELECT Hierarchy.id, Hierarchy.parent_id, Hierarchy.Name
FROM Hierarchy
INNER JOIN Rec
ON Hierarchy.id = Rec.parent_Id
)
SELECT string_agg(Name, '->') path
FROM Rec;
| path |
|:---------------------------------:|
| Sirenia->Paenungulata->Afrotheria |
Rextester here
Problem
I am working through an intro SQL textbook and am confused by the following problem, where we are given the table and values: ``` CREATE TABLE LineageTable ( parent INT, id INT, genus_name VARCHAR(30), PRIMARY KEY (id) ); INSERT INTO LineageTable VALUES (3, 1, 'FamilyA'), (2, 4, 'FamilyB'), (7, 2, 'FamilyC'); ``` And I want to write a function that will return a text string representing the path from the a given name to the desired root My Attempt: ``` CREATE FUNCTION LineageTable (input VARCHAR(50)) RETURNS TABLE (input VARCHAR(50)) AS $$ BEGIN RETURN QUERY SELECT input FROM LineageTable1 INNER JOIN LineageTable ON LineageTable.parent = LineageTable.id WHERE LineageTable1.genus_name = LineageTable1.genus_name; END $$ ``` However, I am confused as how to iterate through this table multiple times to string the path together properly. Any ideas? Thanks all!