Hello,
please see below table definition and datas.
From this flat table I need to display (in HTML, out of scope here) a tree of Categories.
At each level (3 of them, not more) the sorting discriminant is PgSort
At top level, by design ParentPage_Id is NULL
At 2d level ParentPage_Id is NOT NULL, but parent of parent is NULL
At 3d level, the item has 2 ancestors
I had some trials, with calculated fields, but I'll have to nest queries, or Window Functions.
A long time ago, i implemented this successfully in Access/VBA, but I think I need some help.
(I didn't practise SQL for some years, I'm back)
Output to be kind of :
_____________________________
PgPost / PgPost / PgPost
10
10-10
10-20
10-20-10
10-20-25
10-30
10-30-10
10-30-15
10-30-35
20
20-05
20-05-10
20-05-15
_______________________________
Can someone give me just a hint, nudge, good example of nested queries or window function...?
Thanks on forward.
Pierre-Yves Delens (.be)
CREATE TABLE "PagesTreeTT1" (
"ID_Page" INTEGER,
"TxtID_Page" TEXT(25) NOT NULL,
"ParentPage_Id" INTEGER,
"PgSlug" TEXT(120) NOT NULL,
"PgDescr" TEXT(200),
"PgSort" integer(2) DEFAULT 50,
PRIMARY KEY ("ID_Page")
);
sqlite> SELECT * FROM PagesTreeTT1;
+---------+-------------+---------------+--------+----------------------------+--------+
| ID_Page | TxtID_Page | ParentPage_Id | PgSlug | PgDescr | PgSort |
+---------+-------------+---------------+--------+----------------------------+--------+
| 2 | HistRchch | NULL | h | Histoire d'une recherche | 10 |
| 3 | TrvLieu | NULL | t | Trouver un lieu | 10 |
| 4 | GDN_ | 2 | g | Gedinne - et rien d'autre | 20 |
| 5 | gTrv | 2 | j | J'ai trouvé | 30 |
| 6 | dots | 2 | . | ... | 40 |
| 7 | decouvert | 2 | d | Découverte | 20 |
| 8 | testAccent1 | NULL | a | aaa éàçè | 10 |
| 9 | testAccent2 | 7 | b | bbb &$^~ | 20 |
| 10 | SsLaForge | 7 | s | Sous la Forge - Copinette- | 30 |
| 11 | inaug | 7 | i | Inauguration | 40 |
| 12 | chantier_ | NULL | u | Un chantier? | 50 |
| 13 | autour | NULL | e | Et autour de ce site | 60 |
| 14 | infos | NULL | ii | Infos utiles | 70 |
| 15 | WillVlg | 14 | w | Willerzie (village) | 10 |
| 16 | GdnCmn | 14 | gg | Gedinne (Commune) | 20 |
| 17 | commerces | 14 | c | Commerces | 30 |
| 18 | services | 14 | s | Services | 40 |
| 19 | VieLocale- | 14 | v | Vie locale et régionale | 50 |
| 20 | SocioEco | 19 | ss | Socio-économie | 10 |
| 21 | culturel | 19 | cc | Culturel | 20 |
| 22 | politik | 19 | p | Politique | 30 |
| 23 | patrimn | 19 | pp | Patrimoine | 40 |
| 24 | nature | 19 | n | Nature | 50 |
| 25 | about | NULL | a | A propos | 95 |
| 26 | hebergt | 18 | hh | Hébergements | 10 |
| 27 | restaur | 18 | r | Restauration | 20 |
| 28 | SportAct | 18 | sa | Sports - Activités | 30 |
| 29 | brol | 18 | bb | brol | 35 |
| 30 | bril | 18 | bbb | bril | 36 |
+---------+-------------+---------------+--------+----------------------------+--------+