• This forum has a zero tolerance policy regarding spam. If you register here to publish advertising, your user account will be deleted without further questions.

mysql Abfrage über mehrere Tabellen

Nalia

New Member
Hallo,
ich bin neu hier im Forum und hoffe, dass ich hier richtig bin und mir irgendwer helfen kann.
Folgende Situation:
Ich habe drei Tabellen mit folgender (hier benötigter) Struktur:
pferde [rid,cid]
rassenfelle[rid,cid]
colors[id,farbe]
Ich möchte folgende Abfrage ausführen: Anzahl der Pferde mit den jeweiligen Fellen einer bestimmten Rasse, also beispielsweise: Fuchs = 3, Rappe = 10, Brauner = 12 der Rasse Hannoveraner.
Nach endloser Bastelei habe ich nun folgende Abfrage, aber die funktioniert auch nicht...

select colors.farbe from colors, count(*) from pferde as total
left join rassenfelle on rassenfelle.cid = colors.id
left join colors on pferde.cid = colors.id
where rassenfelle.rid = 20

Kann mir jemand auf die Sprünge helfen?
 
Bei solchen "Aufgaben" wäre es immer praktisch wenn eine SQL-Struktur mitgeliefert wird was das Antworten ungemein vereinfacht.
Fangen wir damit mal an:
SQL:
CREATE TABLE pferde(rid integer, cid integer);
CREATE TABLE rassenfelle(rid integer, cid integer);
CREATE TABLE colors(id integer, farbe VARCHAR(50));

INSERT INTO rassenfelle(rid,cid) VALUES (1,10), (1,11), (2,11), (3,12);
INSERT INTO pferde (rid, cid) VALUES (1,10), (2,10), (1,10), (2, 11), (3,12), (1,13);
INSERT INTO colors (id,farbe) VALUES (10,"braun"),(11,"schwarz"),(12,"grau"),(13,"pink");

Laut "Aufgabenstellung" willst du nur für eine spezifische Rasse und nicht alle Rassen-Farbkombinationen wissen, also wäre das Query über 2 Tabellen und nicht drei soweit ich deine Struktur verstehe, da die Rasse bereits ein Eingabeparameter ist. Wir wissen ja bereits durch pferde.rid welche Rasse deine Pferde haben und durch pferde.cid welche Farben, die ganzen Informationen sind also bereits in der Tabelle "pferde" enthalten.
Ohne FarbID->Farbe aufzulösen wäre der Query ganz simpel
SQL:
SELECT COUNT(rid) AS cnt,cid FROM pferde WHERE pferde.rid=1 GROUP BY pferde.cid

Um auch den Farbnamen gleichzeitig zu erhalten, brauchen wir einen simplen Inner Join (keinen OUTER Join).
Unterschiede der Join: https://www.w3schools.com/mysql/mysql_join.asp
SQL:
SELECT COUNT(pferde.rid) AS cnt,colors.farbe FROM pferde, colors WHERE pferde.rid=1 AND pferde.cid=colors.id GROUP BY colors.farbe


Meine Vermutung zur Aufgabe:
- Entweder ich habe dir grade deine Schulaufgabe versucht zu lösen
- Oder du hast ohne Wissen von SQL wild rumprobiert?
 
gaaanz falsch geraten...
Danke für deine Mühe, es klappt jetzt (hatte ich heute morgen und halb 6 tatsächlich selber herausgefunden...) Ich schreibe nicht wirklich oft in Foren und weiß daher nicht, wie man solche Anfragen am besten stellt, aber ich bin ja lernfähig.
Zu deiner Vermutung:
- als ich zur Schule ging, da gab es noch gar keine Computer...
- das, was ich derzeit über SQL weiß, habe ich mir mühsam selbst im hohen Greisenalter angeeignet, ich hoffe, es sei mir deshalb verziehen, dass ich so spezielle Sachen nicht einfach so locker aus dem Ärmel schütteln kann.
Hab Dank und bleib gesund!
 
gaaanz falsch geraten...
Mea culpa. Deine Problemstellung hat, bis auf die zu kurzen Spaltennamen, sehr stark einem Schulproblem geähnelt. Gepaart mit sehr wenigen Informationen, ist es leider durchaus üblich dass man einem faulen Schüler/Studierenden die Aufgabe löst.

- das, was ich derzeit über SQL weiß, habe ich mir mühsam selbst im hohen Greisenalter angeeignet, ich hoffe, es sei mir deshalb verziehen, dass ich so spezielle Sachen nicht einfach so locker aus dem Ärmel schütteln kann.
Forenteilnehmer verfahren generell nach dem Ententest: Wenn es aussieht wie eine Ente, schwimmt wie eine Ente und quakt wie eine Ente, dann ist es vermutlich eine Ente. Dass die Schlussfolgerung kein Beweis ist, haben wir soeben bewiesen. War durchaus nicht böse gemeint.

Ich schreibe nicht wirklich oft in Foren und weiß daher nicht, wie man solche Anfragen am besten stellt, aber ich bin ja lernfähig.
Es ist durchaus Erfahrung notwendig, zu wissen was gebraucht wird, wie es zu formulieren ist und wo. Der Unterschied zwischen "ich kann, ich will aber nicht" und "ich lerne es noch" ist leider sehr gering und letztere Gruppe durchaus predominant. Wenn man wiederholt zum Narren gehalten wurde, entwickelt man einen gewissen Anfangsverdacht.



So, um noch was produktives zur Konversation bei zu tragen;
Die meisten SQL-Datenbanken haben sehr performante SQL-Optimiser, will heissen generell wird verstanden was du willst, und der Query intern in eine andere Struktur umgebaut. Damit das funktioniert, brauchst du aber Indizen über alle abgerufenen Kolonnen damit der Join generell in-memory und nicht über eine temporäre Tabelle erfolgen kann.
Bei diesem Query zB:
SQL:
SELECT COUNT(pferde.rid) AS cnt,colors.farbe FROM pferde, colors WHERE pferde.rid=1 AND pferde.cid=colors.id GROUP BY colors.farbe
bräuchtest du zur Optimisierung einen UNIQUE-Index (oder besser: Primary Key) über colors.id sowie einen INDEX über pferde.rid.

Zur Datenintegrität sind insgesamt UNIQUE-Index noch hilfreich, bspw multi-column über rassenfelle(rid,cid) und über colors.farbe. Dies garantiert dass nicht doppelte Daten ungewollt eingetragen werden können.
 
also...
meine Tabellen haben alle einen primary Key. Das mit dem Index hatte ich bisher noch nie gehört, habe aber - brav wie ich bin - sofort nachgelesen und verstehe so grob, wie das funktionieren soll. Meine Datenbanken, die ich für meine diversen Projekte benutze, sind nicht sooo umfangreich, dass man irgendwelche Verzögerungen bei den Abfragen spürt. Werde aber morgen gleich mal mit dem Index rumexperimentieren.
Die meisten SQL-Datenbanken haben sehr performante SQL-Optimiser, will heissen generell wird verstanden was du willst, und der Query intern in eine andere Struktur umgebaut. Damit das funktioniert, brauchst du aber Indizen über alle abgerufenen Kolonnen damit der Join generell in-memory und nicht über eine temporäre Tabelle erfolgen kann.
Meine Datenbank versteht nicht, was ich ihr sagen will. Ich stehe immer nur vor einem leeren Monitor und wundere mich, was ich falsch gemacht habe und kein Ergebnis bekomme. Selbst eine error-msg kommt nicht immer, so dass ich überhaupt keinen Anhaltspunkt habe, was da nicht funktioniert.
Aber egal, das hab ich bisher immer irgendwie hinbekommen mit viel Nachlesen und ausprobieren. Nur mit den ganzen unterschiedlichen joins stehe ich auf Kriegsfuß, wann man was und wo anwendet. Und count und group by hatte ich bis zu diesem Problem oben überhaupt noch nie benutzt.

SELECT COUNT(pferde.rid) AS cnt,colors.farbe FROM pferde, colors WHERE pferde.rid=1 AND pferde.cid=colors.id GROUP BY colors.farbe
Ich habe dein Select ausprobiert, aber er hat nicht zu dem gewünschten Ergebnis geführt, weil er nicht anzeigt, ob alle Rassenfelle der bestimmten Rasse in der Datenbank Pferde anzeigt, also falls ich ein Pferd mit einer bestimmten Farbe noch nicht habe, sehe ich das nicht. Das habe ich wie folgt hinbekommen:

Select colors.farbe, count(pferde.cid) from colors
left join rassenfelle on colors.id = rassenfelle.cid
left join pferde on (rassenfelle.cid = pferde.cid and pferde.rid=20)
where rassenfelle.rid=20 group by farbe

Damit bin ich glücklich.
btw. wie macht man das, dass dieses Sql mit so einem orangenen Balken links erscheint?
LG
 
btw. wie macht man das, dass dieses Sql mit so einem orangenen Balken links erscheint?
Beim Antworten/Beitrag erstellen ganz rechts in der Buttonleiste auf Drei-Punkte-Button, dort Code wählen, Code einfügen und SQL als Codeformat wählen.
Dann wird es so wie hier:
SQL:
Select colors.farbe, count(pferde.cid) from colors
left join rassenfelle on colors.id = rassenfelle.cid
left join pferde on (rassenfelle.cid = pferde.cid and pferde.rid=20)
where rassenfelle.rid=20 group by farbe
 
Ich habe dein Select ausprobiert, aber er hat nicht zu dem gewünschten Ergebnis geführt, weil er nicht anzeigt, ob alle Rassenfelle der bestimmten Rasse in der Datenbank Pferde anzeigt, also falls ich ein Pferd mit einer bestimmten Farbe noch nicht habe, sehe ich das nicht.
Gute Anmerkung und sehr gutes Beispiel warum du doch einen OUTER Join brauchst; du kannst eine oder mehrere Zeilen haben welche keine Gegenstücke in den anderen Tabellen haben.

Meine Datenbank versteht nicht, was ich ihr sagen will. Ich stehe immer nur vor einem leeren Monitor und wundere mich, was ich falsch gemacht habe und kein Ergebnis bekomme.
Es gibt die "EXPLAIN" und "ANALYZE" Befehle aber das hilft eher bei Performance-Problemen als Verständnisproblemen zwischen Mensch und Machine. Meistens gewinnt die Machine in der Sturheit :p

Meine Datenbanken, die ich für meine diversen Projekte benutze, sind nicht sooo umfangreich, dass man irgendwelche Verzögerungen bei den Abfragen spürt.
Das ist bei aktuellen Rechner generell sogar für grössere Datenbanken oft wahr. Allerdings gehe ich davon aus dass du tatsächlich eine saubere Lösung haben willst statt "it works", sonst hättest du nach genereller Einstiegs-Methode jede Tabelle einzeln komplett in dein Programm eingelesen und dort dann in einer while()-Konstruktion zusammenkombiniert. Zusätzlich sei gesagt dass Relationen leistungsfähig sind aber trotzdem recht kostenintensiv da MySQL über mehrere Tabellen hantieren muss. Hier ein kleiner Erklärversuch der Relationen:

Vorweg; ich bin weder Datenbank-Modellierer noch beruflich -Administrator. Die Kenntnisse kommen auch aus Selbststudium und sind vermutlich unvollständig.

Ich bevorzuge Datenbank-Design über visuelle Tools zu verwirklichen, ansonsten verliere ich mich hoffnungslos sobald die Struktur etwas komplexer wird. Für Mysql wäre das Tool der Wahl generell "MySQL Workbench":
Grafisch dargestellt wären deine Tabellen wie folgend (Ich habe mir etwas Freiheiten bei den Spaltennamen genommen und rassenfelle von der Rasse getrennt um ein "Schulbeispiel" zu haben)
1643743855790.png


Zuerst mal hat jede Tabelle natürlich einen Primary-Key "id" welcher dadurch automatisch Non-Null und Unique ist. Du kannst also jede ID nur einmal haben, aber ich könnte mehrere Zeilen mit dem gleichen Inhalt haben, deshalb sind farben.farbe und rassen.name jeweils ein UNIQUE-Index. Dann ist auf Rassenfelle noch ein UNIQUE(rassenid,farbenid)-Index welcher sicherstellt dass wir jedes Rassenfell nur einmal listen.

Alle hier dargestellten (Foreignkey) Relationen zwischen den Tabellen sind 1:n, will heissen:
- pferde-n:1->farben: Wir können n Pferde mit der gleichen Farbe haben
- pferde-n:1->rassenfelle: Wir können n Pferde mit der gleichen Rasse haben
- rassenfelle-n:1->farben: Wir können n Rassen mit der gleichen Farbe haben
- rassenfelle-n:1->rassen: Wir können n Rassenfelle mit der gleichen Rasse haben

Die Relationen garantieren:
- die jeweilige Relation muss existieren. Bei Nicht-Bestehen einer farbenid beim Anlegen eines Pferdes wird der SQL Insert verweigert
- die jeweilige "ZielID" kann "einfach so" weder geändert noch gelöscht werden solange es Abhängigkeiten gibt
- Mysql "versteht" was du willst und kann das Query intern besser anpassen.

Falls du eine Relation sehr oft brauchst (zB pferde ->rassenfelle ) kann man die Queries dazu in fiktiven SQL-Tabellen speichern, sogenannten VIEWS. Diese fiktive Tabelle kann danach über SQL-Befehle abgerufen werden, als wäre es eine normale Tabelle, nur dass die Daten sauber jeweils nur 1x existieren und faktisch getrennt sind (DRY - don't repeat yourself).
 
Bei größeren Projekten ist immer ein GUI wie bspw. MySQL Workbench o. ä. sinnvoll, um Datenbanken, Tabellen und Relationen darzustellen.
 
Back
Top