Mehrfachabfrage in einer mysql Tabelle, doppelte Ergebnisse

[Hennak]

Irgendwie ist bei mir da der totale Wurm drin.

id !ebene! name
 1 !  1  ! Niedersachsen
 2 !  2  ! Hannover
 3 !  2  ! Hildesheim
 4 !  2  ! Göttingen
 5 !  1  ! Thüringen

So werden alle Orte aus Niedersachsen ausgegeben.

SELECT ebene, name  FROM city WHERE ebene = 2  ORDER BY name ASC;

Und so sollten zur Stadt noch der Eintrag Land dazukommen

SELECT a.ebene, a.name, b.name AS land  FROM city a 
LEFT JOIN city b ON  b.ebene =1 
WHERE  a.ebene = 2;

Bei mir werden aber alle Städte mehrfach ausgegeben. Nämlich mit allen Ländern. In meinem Beispiel

Hannover Niedersachsen Hannover Thüringen Hildesheim Niedersachsen Hildesheim Thüringen

Weis jemand was da falsch läuft?

Hennak

[Rolf B]

Hallo Hennak,

Weis jemand was da falsch läuft?

Ja. Deine Datenbank enthält keine Information über die Beziehung zwischen Land und Stadt. Deine SQL Abfrage liefert ein kartesisches Produkt aller Ebene-1 Objekte mit den Ebene-2 Objekten.

So werden alle Orte aus Niedersachsen ausgegeben.

SELECT ebene, name  FROM city WHERE ebene = 2  ORDER BY name ASC;

NEIN. So werden alle Orte ausgegeben. Egal in welchem Bundesland. Deine Tabelle enthält derzeit nur Orte aus Niedersachsen, deswegen scheint es zu funktionieren. Aber füge mal Erfurt und Jena hinzu.

Also - irgendwie muss Hannover "wissen", dass es zu Niedersachsen gehört, und Erfurt, dass es zu Thüringen gehört. Dieses Wissen musst Du der Tabelle hinzufügen und in der ON-Klausel der SQL Query nutzen.

Rolf

[Kurt]

Hallo,

Also - irgendwie muss Hannover "wissen", dass es zu Niedersachsen gehört, und Erfurt, dass es zu Thüringen gehört.

Sinnvollerweise würde ich das auch nicht in dieselbe Tabelle schreiben. Eine Tabelle "Bundesländer" zu einer Tabelle "Staedte" (eigentlich sogar nebst Zuordnungstabelle) klingt für mich sinnvoll und auch "normalisiert".

Kurt

[Rolf B]

Hallo Kurt,

das hängt von den Anforderungen ab. Wenn ich eine hierarchische Struktur ähnlicher Objekte habe, kann man das auch in eine gemeinsame Tabelle packen. Das muss man im Einzelfall genau betrachten.

Vor allem, wenn die Struktur nicht homogen ist. Eigene Tabellen für jeden Typ von Gebietskörperschaft könnten die Navigation in der Datenbank erschweren.

Rolf

[Raketenwilli]

Weis jemand was da falsch läuft?

Ja. Viel.

Zuerst hat Deine Tabelle ein Problem. Es fehlt eine Beziehung zwischen den Orten und dem Land. Ich habe eine Spalte "parent" hinzugefügt, dann auch noch Erfurt in Thüringen damit der Effekt sichtbar wird:

BEGIN TRANSACTION;
CREATE TABLE IF NOT EXISTS "orte" (
	"id"	INTEGER,
	"parent"	INTEGER,
	"ebene"	INTEGER,
	"name"	TEXT,
	PRIMARY KEY("id")
);
INSERT INTO "orte" VALUES (1,NULL,1,'Niedersachsen');
INSERT INTO "orte" VALUES (2,1,2,'Hannover');
INSERT INTO "orte" VALUES (3,1,2,'Hildesheim');
INSERT INTO "orte" VALUES (4,1,2,'Göttingen');
INSERT INTO "orte" VALUES (5,NULL,1,'Thüringen');
INSERT INTO "orte" VALUES (6,5,2,'Erfurt');
COMMIT;

Die Abfrage geht dann via inner Join

SELECT a.name as City, b.name AS Country  
FROM 
	orte AS a,
	orte AS b
WHERE
	a.parent = b.id

liefert (die von mir) erwartete Tabelle mit allen Städten und den dazu gehörenden Bundesländern.

Zur weiteren Eingrenzung kannst Du dann ETWAS wie

AND City="Erfurt"

oder

AND Country="Niedersachsen"

zur where-Clausel hinzufügen.

[MudGuard]

Hi,

Zur weiteren Eingrenzung kannst Du dann ETWAS wie

AND City="Erfurt"

oder

AND Country="Niedersachsen"

zur where-Clausel hinzufügen.

columns not found.

Es müßte auf name = ... abgefragt werden, und das wäre dann nicht eindeutig, weil's ja sowohl in a als auch in b eine name-Spalte gibt.

Es müßte also für Stadt a.name = ... und für's Land b.name = ... sein.

cu,
Andreas a/k/a MudGuard

[Raketenwilli]

Hi,

Zur weiteren Eingrenzung kannst Du dann ETWAS wie

AND City="Erfurt"

oder

AND Country="Niedersachsen"

zur where-Clausel hinzufügen.

columns not found.

Dann hast Du etwas anders gemacht als ich:

(Sqlite3) - Hast Du womöglich GROSS/klein vertan?

[Rolf B]

Hallo Raketenwilli,

erstaunlich, dass man in einem WHERE auf die Aliasnamen im SELECT Bezug nehmen können soll.

Mein Datenmariechen der Version 10.5.9 kann's jedenfalls nicht, und ich hab auch nichts anderes erwartet. MySQL wird es nicht anders machen, und ein SQLite habe ich gerade nicht am Start.

Aliasnamen werden erst ganz am Schluss vergeben, EIGENTLICH sogar erst nach dem ORDER BY. Ich arbeite primär mit DB2 und MS SQL Server, dort kann man weder im GROUP BY noch im ORDER BY Bezug auf Aliasnamen nehmen.

An der Stelle weichen MYSQL/MariaDB ab, die vergeben die Aliasnamen bereits vor dem GROUP BY. Aber nicht früher; im WHERE gibt es sie noch nocht.

Rolf

[Raketenwilli]

erstaunlich, dass man in einem WHERE auf die Aliasnamen im SELECT Bezug nehmen können soll.

Ich empfinde das nicht „erstaunlich“ sondern als „konsequent“ und in sqlite3 geht es. Es sei denn der sqlitebrowser „übersetzt“ das SQL.

Geht also. Geht also ebenso nicht. In MariaDB kann ich nicht mal das gezeigte Skript zur Erstellung der Tabelle ausführen… Wodurch dann klar wird, warum es „Structured Query Language“ statt „Standarded Query Language“ heißt.

Gut, dass ich

notiert habe. 🙃😀😱😷

Nicht des zu trotz ändert das nichts daran, dass hier ein Inner Join her muss und im Original die Spalte für die Zuordnung zur „übergeordneten Gebietskörperschaft“ fehlt.

Aber: Es gibt in Hinblick auf denkbare Erweiterungen (und Exklaven) den Gedanke daran, dass eine derart hierarchische Zuordnung nicht in jedem Fall sinnvoll ist. Frankreich z.B. liegt nach allgemeiner Ansicht zwar in Europa - das gilt aber nicht für jeden Ort Frankreichs.

Aus solchen Gründen würde ich dazu raten, die Länder in eine eigene Tabelle zu packen. Das lässt sich später auch besser bearbeiten.

ein SQLite habe ich gerade nicht am Start

Hm. Das brauch ich immer öfter… und es ist nicht viel (ein paar MB). Firefox nutzt es z.B. im Profil.

[Rolf B]

Hallo Raketenwilli,

Nicht des zu trotz ändert das nichts daran, dass hier ein Inner Join her muss und im Original die Spalte für die Zuordnung zur „übergeordneten Gebietskörperschaft“ fehlt.

Das ist unbestritten, und ich schrieb es gestern schon eine Viertelstunde früher als du 😝😉

hierarchische Zuordnung nicht in jedem Fall sinnvoll

Das stimmt auch. Aber wenn jemand von sich aus nicht drauf kommt, dass eine 1:n Zuordnung fehlt, dann überrolle ich ihn nicht gleich mit einer m:n Relationstabelle. Bislang fehlt jeder Mucks von Hennak, ob ihrm^[1] das geholfen hat.

Das lässt sich später auch besser bearbeiten.

Dem würde ich widersprechen. Die Pflege einer m:n Relationstabelle ist deutlich aufwändiger - unter anderem, weil auch der fachliche Sachverhalt komplexer ist. Und man muss 2 Tabellen manipulieren, nicht nur eine. Deswegen würde ich so etwas nicht ohne Not konstruieren.

Rolf

---

1. Pronomenvorschlag 3. Person Neutral: sier (sie+er), sihn (sein+ihr), ihrm (ihm+ihr), sier (sie+er) ↩︎

[MudGuard]

Hi,

Dann hast Du etwas anders gemacht als ich:

Ich hab den Betreff des Original-Postings beachtet.

Nix Sqlite3, sondern MySQL.

cu,
Andreas a/k/a MudGuard