CODEWITHSUNDEEP
sql
pouzzler
pouzzler

Reputation: 1834

sql get a tree from hierarchical tables

if you have a hierarchy of tables, each item in a table containing a reference to the id of its parent in the parent table, is there a way to obtain that tree from a single (few) sql query as a tree?

Something looking like:

SELECT *
FROM specy
JOIN genus ON genus.id=specy.genus
JOIN family ON genus.family=family.id

But even if that was correct syntactically, would it not return a dataset with a family field for each genus of this family, and both a family and a genus field for each specy of that genus?

Can SQL return a hierarchical tree directly (easily), or only a dataset which I must then process in another language?

Upvotes: 0

Views: 100

Answers (2)

Barmar
Barmar

Reputation: 782407

If you don't want to see the redundant information you can order the results by family and genus, then use user variables to replace duplicates from the previous row with blanks. You'll then only see the family and genus names when they change.

SELECT CASE WHEN f.id = @prev_family THEN '' ELSE f.name END AS family,
       CASE WHEN g.id = @prev_genus THEN '' ELSE g.name END AS genus,
       s.name AS species
FROM specy AS s
JOIN genus AS g ON s.genus = g.id
JOIN family AS f ON g.family = f.id
CROSS JOIN (SELECT @prev_family := 0, @prev_genus := 0) AS vars
ORDER BY f.id, g.id, s.id

Upvotes: 0

Racil Hilan
Racil Hilan

Reputation: 25361

Yes, SQL can return hierarchical data using common table expressions (CTEs), but not all databases support CTEs. The syntax is:

WITH [RECURSIVE] with_query [, ...]
SELECT...

Example:

WITH RECURSIVE table1 (n, fact) AS 
(SELECT col1+1, col1*col2 FROM table1)
SELECT * FROM table1;

Upvotes: 1

Related Questions