다 대다 관계가있는 두 테이블에 대한 CTE 재귀 쿼리

결과를 제대로 반환하려면 단일 쿼리가 필요한 세 개의 테이블이 있습니다.

첫 번째 테이블은 상위 및 하위 카테고리를 포함하는 카테고리 테이블입니다. 이 테이블은 하위 범주의 하위를 포함하여 모든 수준의 하위 테이블을 허용합니다.

두 번째 테이블은 카테고리에 속할 수있는 파일 테이블입니다. 이것은 범주 테이블과 다 대다 관계를 갖습니다. 파일은 하위 범주를 포함하는 범주에 속할 수도 있습니다.

세 번째 테이블은 파일과 범주 간의 다 대다 관계를 만듭니다.

첫 번째 테이블은 Categories다음과 같습니다.

CREATE TABLE Categories (
  category_id INT NOT NULL PRIMARY KEY,
  category_name varchar(20) NOT NULL,
  parent_id INT NOT NULL
);

두 번째 테이블은 Files

CREATE TABLE Files (
  file_id INT NOT NULL PRIMARY KEY,
  file_name varchar(20) NOT NULL
);

세 번째 테이블은 파일을 범주에 연결합니다. 파일은 모든 범주에 속할 수 있으며 여러 범주에 속할 수 있습니다.

CREATE TABLE Category_File (
  category_id INT NOT NULL,
  file_id INT NOT NULL
);

테이블은 다음과 같이 채워집니다.

INSERT INTO Categories (category_id, category_name, parent_id) VALUES
(1, 'Cat1', 0),(2, 'Cat2', 0),(3, 'Cat3', 1),
(4, 'Cat4', 2),(5, 'Cat5', 1),(6, 'Cat6', 0),
(7, 'Cat7', 5),(8, 'Cat8', 4),(9, 'Cat9', 7);

INSERT INTO Files (file_id, file_name) VALUES
(1, 'File1'),(2, 'File2'),(3, 'File3'),
(4, 'File4'),(5, 'File5'),(6, 'File6');

INSERT INTO Category_File (category_id, file_id) VALUES
(3, 1),(4, 2),(5, 3),
(9, 6),(7, 2),(5, 4),
(8, 4),(6, 1),(3, 5);

이름순으로 정렬 된 카테고리와 이름순으로 정렬 된 파일을 표시하려면 리턴이 필요합니다. 따라서 모든 열이있는 모든 레코드를 쿼리하면

Result Set 1
[
category_id,    category_name,  file_id,    file_name   parent_id
1               Cat1            Null        Null        0
3               Cat3            Null        Null        1
3               Cat3            1           File1       Null
3               Cat3            5           File5       Null
5               Cat5            Null        Null        1   
5               Cat5            3           File3       Null    
5               Cat5            4           File4       Null
7               Cat7            Null        Null        5
7               Cat7            2           File2       Null
9               Cat9            Null        Null        7
9               Cat9            6           File6       Null
2               Cat2            Null        Null        0
4               Cat4            Null        Null        2
4               Cat4            2           File2       Null
8               Cat8            Null        Null        4
8               Cat8            4           File4       Null
6               Cat6            Null        Null        0
6               Cat6            1           File1       Null
]