Retrieving a Link Tree Using SQL Server

Question

I have a database that manages files - some files contain / link to other files, and my goal is to create a query that can give me the whole "tree" for this document.

For example, a structure might look like this:

• File 1
  • File 2
  • File 3
  • File 4
  • File 5
    • File 6
    • File 7
    • File 8
      • File 9
      • File 10

etc. where File 1 effectively contains all the files following it

They are split in my database between two tables - they allow you to call them the "Files" table and the "Links" table

In the "Files" table there is information about the files themselves - FileID, Filename, etc.

"" . , , , 6 1 - 5.

:.

[ParentFileID]  [ChildFileID]

1               2
1               3
1               4
1               5
5               6
5               7
5               8
8               9
8               10

FileID,

? CTE, , - - , , , , - NULL .

Answer 1

, CTE.

USE tempdb
GO
CREATE TABLE files
(
    [file_id] int PRIMARY KEY,
    [file_name] varchar(128) NOT NULL
);
INSERT INTO files VALUES
(1, 'File 1'),
(2, 'File 2'),
(3, 'File 3'),
(4, 'File 4'),
(5, 'File 5'),
(6, 'File 6'),
(7, 'File 7'),
(8, 'File 8'),
(9, 'File 9'),
(10, 'File 10');


CREATE TABLE [references]
(
    parent_file_id int NOT NULL,
    child_file_id int NOT NULL,
    PRIMARY KEY (child_file_id) 
);
INSERT INTO [references] VALUES
(1, 2),
(1, 3),
(1, 4),
(1, 5),
(5, 6),
(5, 7),
(5, 8),
(8, 9),
(8, 10);
GO

CREATE FUNCTION dbo.get_file_with_path(@file_id int)
RETURNS TABLE
AS
RETURN WITH h
AS
(
    SELECT 
        f.file_id, f.file_id as child_file_id, 
        f.file_name, 0 as reverse_level, 
        CAST( '/' + f.file_name as varchar(8000)) as path
    FROM 
        dbo.files f

    WHERE
        f.file_id = @file_id

    UNION ALL

    SELECT 
        h.file_id, r.parent_file_id as child_file_id,
        h.file_name, h.reverse_level + 1 as reverse_level,
        CAST('/' + f.file_name + h.path as varchar(8000)) as path
    FROM
        h 
        INNER JOIN  [references] r
            ON h.child_file_id = r.child_file_id
        INNER JOIN dbo.files f
            ON f.file_id = r.parent_file_id
)
SELECT TOP(1) h.file_id, h.file_name, h.path 
FROM h 
ORDER BY h.reverse_level DESC;
GO

SELECT *
FROM dbo.get_file_with_path(1)
UNION ALL
SELECT *
FROM dbo.get_file_with_path(3)
UNION ALL
SELECT *
FROM dbo.get_file_with_path(6)
UNION ALL
SELECT *
FROM dbo.get_file_with_path(10)

:

| file_id | file_name |             path              |
|---------|-----------|-------------------------------|
|       1 | File 1    | /File 1                       |
|       3 | File 3    | /File 1/File 3                |
|       6 | File 6    | /File 1/File 5/File 6         |
|      10 | File 10   | /File 1/File 5/File 8/File 10 |

, , position

EDIT:

, , node:

CREATE FUNCTION dbo.get_file_subtree_excluding_self(@file_id int)
RETURNS TABLE
AS RETURN
WITH h AS
(
    SELECT r.parent_file_id, r.child_file_id
    FROM [references] r
    WHERE r.parent_file_id = @file_id

    UNION ALL

    SELECT r.parent_file_id, r.child_file_id
    FROM
        h INNER JOIN [references] r
            ON h.child_file_id = r.parent_file_id

)
SELECT h.child_file_id as [file_id]
FROM h
GO

SELECT * FROM dbo.get_file_subtree_excluding_self(5)

:

+---------+
| file_id |
+---------+
|       6 |
|       7 |
|       8 |
|       9 |
|      10 |
+---------+

references . node - , . , :

+-------+--------+
| child | parent |
+-------+--------+
|     1 |      2 |
|     2 |      3 |
|     3 |      1 |
+-------+--------+

, .