Recursive Queries syntax is very similiar to SQL Server (using Common Table Expressions). There are many articles about it.
For example, create a table and populate with some rows:
Hierarchical relations can be expressed as a recursive single tables' join. For example, consider table:
CREATE TABLE objects ( id INT NOT NULL PRIMARY KEY partition, name VARCHAR, parent_id INT ); GO; -- insert hierarchical data INSERT INTO objects(id,name,parent_id) VALUES(1,'Cars',NULL); INSERT INTO objects(id,name,parent_id) VALUES(2,'Porsche',1); INSERT INTO objects(id,name,parent_id) VALUES(3,'911',2); INSERT INTO objects(id,name,parent_id) VALUES(4,'Boxster',2); INSERT INTO objects(id,name,parent_id) VALUES(5,'Cayman',2); INSERT INTO objects(id,name,parent_id) VALUES(6,'Cayenne',2); INSERT INTO objects(id,name,parent_id) VALUES(7,'Panamera',2); INSERT INTO objects(id,name,parent_id) VALUES(8,'Aston Martin',1); INSERT INTO objects(id,name,parent_id) VALUES(9,'DB7',8); INSERT INTO objects(id,name,parent_id) VALUES(10,'DB9',8); INSERT INTO objects(id,name,parent_id) VALUES(11,'Vantage',8); INSERT INTO objects(id,name,parent_id) VALUES(12,'One',8);
Here we have created hierarchy using parent_id field that refers to the parent row: "Cars" is the root (no parent), 2 brands (Porsche and Aston Martin) belongs to "Cars" and each brand has a list of models. Now, define the recursive CTE SQL to read all porsche models:
WITH Cars(id,parent_id,name,path,level) AS ( --initialization. read porsche root SELECT id,parent_id,name,name,0 AS level FROM objects WHERE id = 2 -- porsche root UNION ALL --recursive execution SELECT o.id,o.parent_id,o.name,concat(c.path,'/', o.name), c.level+1 FROM cars c INNER JOIN objects o ON c.id = o.parent_id ) SELECT * FROM cars;
The result:
| Id | Parent_Id | Name | Path | Level |
|---|---|---|---|---|
| 2 | 1 | Porsche | Porsche | 0 |
| 3 | 2 | 911 | Porsche/911 | 1 |
| 4 | 2 | Boxster | Porsche/Boxster | 1 |
| 5 | 2 | Cayman | Porsche/Cayman | 1 |
| 6 | 2 | Cayenne | Porsche/Cayenne | 1 |
| 7 | 2 | Panamera | Porsche/Panamera | 1 |
Example using parameterized query and ordering:
DECLARE @id INT SET @id = SELECT id FROM objects WHERE name = 'porsche'; WITH Cars(id,parent_id,name,path,level) AS ( --initialization. read porsche root SELECT id,parent_id,name,name,0 AS level FROM objects WHERE id = @id UNION ALL --recursive execution SELECT o.id,o.parent_id,o.name,concat(c.path,'/', o.name), c.level+1 FROM cars c INNER JOIN objects o ON c.id = o.parent_id ) SELECT * FROM cars ORDER BY name;