Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Simple SQL path query?

Tags:

sql

postgresql

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!

like image 581
ABlueCrayon Avatar asked Apr 29 '17 19:04

ABlueCrayon


People also ask

How do I find the path in SQL?

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.

What is a simple SQL query?

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.

How do I find the shortest path in SQL?

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.


1 Answers

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

like image 199
McNets Avatar answered Sep 29 '22 17:09

McNets