AlainIb AlainIb - 4 months ago 9
SQL Question

sql hierarchy in table

i use postgres 9.5.
I need to store a (tree like) data like this :


  • level 1a


    • level 2a


      • level 3a

      • level 3b


    • level 2b


  • level 1b


    • level 2a

    • level 2b


  • level 1c

  • level 1d



this is my sql table:

CREATE TABLE matieres_test (
id serial NOT NULL CONSTRAINT matieres_test_id_pk PRIMARY KEY,
name varchar NOT NULL,
parent INT DEFAULT NULL,
CONSTRAINT matieres_test_parent_fk FOREIGN KEY (parent) REFERENCES matieres_test(id)
);


and insert ( the tabulation if for easy reading )

insert into matieres_test(id,name,parent) values (1,'level 1a',null);
insert into matieres_test(id,name,parent) values (5,'level 2a ',1);
insert into matieres_test(id,name,parent) values (9,'level 3a',5);
insert into matieres_test(id,name,parent) values (10,'level 3b',5);
insert into matieres_test(id,name,parent) values (6,'level 2b', 1);

insert into matieres_test(id,name,parent) values (2,'level 1b',null);
insert into matieres_test(id,name,parent) values (7,'level 2a',2);
insert into matieres_test(id,name,parent) values (8,'level 2b',2);

insert into matieres_test(id,name,parent) values (3,'level 1c',null);
insert into matieres_test(id,name,parent) values (4,'level 1d',null);





NOW i want to get the data, my query :

SELECT t1.name AS lev1, t2.name as lev2, t3.name as lev3, t4.name as lev4
FROM matieres_test AS t1
LEFT JOIN matieres_test AS t2 ON t2.parent = t1.id
LEFT JOIN matieres_test AS t3 ON t3.parent = t2.id
LEFT JOIN matieres_test AS t4 ON t4.parent = t3.id


the results :

| lev1 | lev2 | lev3
----|-----------|-----------|----------
1 | level 1a | level 2a | level 3b
2 | level 1a | level 2a | level 3a
3 | level 2a | level 3b |
4 | level 1b | level 2b |
5 | level 1a | level 2b |
6 | level 1b | level 2a |
7 | level 2a | level 3a |
8 | level 3b |
9 | level 2b |
10 | level 2b |
11 | level 2a |
12 | level 1d |
13 | level 1c |
14 | level 3a |





The problem is it return wrong lines (line 3,7,8,9,10,11,14)
because i get "level 2a" in the "lev1" column etc ...

I need something like this :

| lev1 | lev2 | lev3
----|-----------|-----------|----------
| level 1a | level 2a | level 3a
| level 1a | level 2a | level 3b
| level 1a | level 2b |
| level 1b | level 2a |
| level 1b | level 2b |
| level 1d |
| level 1c |


Any idea please ?

Answer
SELECT DISTINCT
  t1.name AS lev1,
  t2.name AS lev2,
  t3.name AS lev3,
  t4.name AS lev4
FROM matieres_test AS t1
LEFT JOIN matieres_test AS t2
  ON t2.parent = t1.id
LEFT JOIN matieres_test AS t3
  ON t3.parent = t2.id
LEFT JOIN matieres_test AS t4
  ON t4.parent = t3.id
WHERE t1.parent IS NULL
ORDER BY lev1, lev2, lev3, lev4;

You have to add a IS NULL check for the t1.parent. With the given ORDER BY clause you can sort the result correctly.