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!
If you ever need to know where your database files are located, run the following T-SQL code: USE master; SELECT name 'Logical Name', physical_name 'File Location' FROM sys. master_files; This will return a list of all data files and log files for the SQL Server instance.
An SQL query consists of three pieces, or blocks: the select block, the from block and the where block. The select block tells the database which columns of data you want it to return. You must separate each column name with a comma.
The SHORTEST_PATH function will return any one shortest path between nodes. It currently does not support returning all shortest paths between nodes; it also does not support returning all paths between nodes. The SHORTEST_PATH implementation finds an unweighted shortest path.
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
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With