Im Grunde geht es bei den Daten um Land/Bundesland/Landkreis/Stadt.
Land | Bundesland | Landkreis | Stadt | Name
1 0 0 0 Deutschland
1 1 0 0 Baden-Württenberg
1 1 1 0 Stuttgart
1 1 1 1 Stuttgart
1 1 2 0 Heilbronn
1 1 2 2 Heilbronn
Ausgabe:
Land | Bundesland | Landkreis | Stadt
Deutschland | Baden-Württenberg | Stuttgart | Stuttgart
Deutschland | Baden-Württenberg | Heilbronn| Heilbronn
Fragt mich bitte nicht warum die Daten so angeordnet sind, es ist einfach so
Eine Abfrage hab ich inzwischen, allerding gibt es Probleme wenn in der Spalte Stadt keine Werte gibt, dann kann es keine Ausgabe geben. Ich bräuchte eine Abfrage die fehlende Daten mit NULL Werte auffüllt.
Land | Bundesland | Landkreis | Stadt
Deutschland | Baden-Württenberg | Stuttgart | Stuttgart
Deutschland | Baden-Württenberg | Heilbronn| NULL
Die Anordnung der Daten ist mehr als bescheiden und daraus resultiert auch dein Problem, die Anfrage ordentlich zusammenzustellen.
Nun, als einfachster Ausweg würde ich dir eine Stored Procedure empfehlen, die du aufrufst und die intern die eigentliche Arbeit erledigt. Dann kannst du ggf. auch mehrere Abfragen ausführen, um fehlende Daten einzusammeln.
Vielleicht hilft dir auch die neu hinzugekommene Rekursion in PostgreSQL 8.4, obwohl mir da auf Anhieb auch kein sauberer Weg einfällt - die Daten sind einfach zu ungeschickt zusammengestellt.
Wenn Du schon eine Abfrage hast, dann könntest Du diese ja hier zeigen, damit wir nicht das Rad zum 2. Mal erfinden müssen. Die fehlenden Daten mit ‘NULL’ aufzufüllen scheint eine Aufgabe für COALESCE() zu sein.
Für die Typ-Spalte könnte/sollte man noch eine weitere Tabelle nehmen, auf die referenziert wird und die die Tyenbezeichnung (1=Land, 2=Bundesland, …) enthält.
Eine rekursive Abfrage, die das in ‘schön lesbar’ ausgibt könnte dann lauten:
test=*# with recursive re as (select f.* from foo f where name='Deutschland' union all select p.* from foo p join re on (re.id=p.parent_id)) select id, case when typ=1 then 'Land' whentyp=2 then ' Bundesland' when typ=3 then ' Landkreis' when typ=4 then ' Stand' end as Typ, name from re order by id;
id | typ | name
----+-------------+-------------------
1 | Land | Deutschland
2 | Bundesland | Baden-Württenberg
3 | Landkreis | Stuttgart
4 | Stand | Stuttgart
5 | Landkreis | Heilbronn
6 | Stand | Heilbronn
7 | Bundesland | Sachsen
8 | Landkreis | Meißen
9 | Stand | Meißen
(9 rows)
Wie gesagt, die Bezeichnungen könnte man noch in einer weiteren Tabelle halten und diese könnte man hier reinjoinen, um sie da Gehampel mit dem case when … zu ersparen.
Mit dieser Struktur könnte man auch Teilbäume leicht abfragen:
test=*# with recursive re as (select f.* from foo f where name='Baden-Württenberg' and typ=2 union all select p.* from foo p join re on (re.id=p.parent_id)) select id, case when typ=1 then 'Land' when typ=2 then ' Bundesland' when typ=3 then ' Landkreis' when typ=4 then ' Stand' end as Typ, name from re order by id;
id | typ | name
----+-------------+-------------------
2 | Bundesland | Baden-Württenberg
3 | Landkreis | Stuttgart
4 | Stand | Stuttgart
5 | Landkreis | Heilbronn
6 | Stand | Heilbronn
(5 rows)
Also, falls es noch nicht zu spät ist, stricke das noch mal um.
Na, wenn du sie sowieso neu anlegen willst, mach es gleich richtig.
Dann brauchst du dich auch nicht auf eine einzelne Abfrage zum Auslesen der Ursprungsdaten beschränken, die Arbeit passiert ja nur einmalig.
SELECT level1.area_name AS Land, level2.area_name AS Bundesland, level3.area_name AS Landkreis, level4.area_name AS Stadt, level5.area_name AS Stadtteil FROM tabelle1 AS level1, tabelle1 AS level2, tabelle1 as level3, tabelle1 as level4, tabelle1 as level5 WHERE (level1.areacode_1 = level2.areacode_1) AND (level2.areacode_1 = level3.areacode_1 AND level2.areacode_2 = level3.areacode_2) AND (level3.areacode_1 = level4.areacode_1 AND level3.areacode_2 = level4.areacode_2 AND level3.areacode_3 = level4.areacode_3) AND (level4.areacode_1 = level5.areacode_1 AND level4.areacode_2 = level5.areacode_2 AND level4.areacode_3 = level5.areacode_3 AND level4.areacode_4 = level5.areacode_4) ORDER BY level5.area_name