nicht angemeldet
SQL über 3 Tabellen mittels Join (mysql-db)
Hallo zusammen,
ich bin derzeit an der Erstellung einer Internetseite für unsere wöchentliches Bundesliga-Tippspiel um mich auch mal in PHP und SQL wieder reinzufuchsen...
Nun bin ich aber auf ein kleines problem gestoßen und bräuchte eure hilfe bei einem SQL-Statement, ob dies mit einem RIGHT/LEFT join möglich ist.
Ich habe 3 Tabellen mit folgenden Strukturen:
-
Users (für die Spieler)
--------------------------
+UserID(int, Primary Key)
+UserName(varchar)
+aktiv(aktiv) -> dient für spätere Historie um auch Saisonübergreifend Tipps von Spielern beizubehalten -
spieltag (für die Speicherung der wirklichen Partien und deren Ergebnisse)
------------------------------------------------------------------------------
+ID (int, Primary Key)
+Heim (int, Fremdschlüssel zur TeamID einer anderen Tabelle)
+Gast (int, Fremdschlüssel zur TeamID einer anderen Tabelle)
+ToreHeim (int)
+ToreGast (int)
+spieltag (int) -
spieltagtippen (Hier sind die tipps der Spieler)
----------------------------------------------------
- IDSpieler(int, Primary Key)
- IDSpiel(int, Primary Key)
- TippToreHeim (int)
- TippToreGast (int)
- spieltag (int)
Für die Auswertung habe ich nun folgendes vor:
----------------------------------------------
Ich möchte, um es später für die Darstellung in PHP einfacher zu haben, der Datenbank die Arbeit überlassen.
Ziel ist es, dass ich Alle Spiele eines Spieltages mit sämtlichen Tipps mir darstellen lasse, auch von denen, die nicht getippt haben (deise sind auch nicht in der tabelle spieltagtippen enthalten)!
Mit dem SQL-Statement:
SELECT ID, Heim, Gast, ToreHeim, ToreGast, UserID, UserName
FROM `spieltag`
CROSS JOIN users
WHERE aktiv = true
AND spieltag =1
ORDER BY UserID ASC , ID ASC
bekomme ich dann folgendes Ergebnis:
Alle Spiele eines Spieltages |Jeder Nutzer (auswahl User 2)
---------------------------------------------------|---------------------------
ID Heim Gast ToreHeim ToreGast | UserID UserName
33 75 51 NULL NULL | 2 daniel
34 56 66 NULL NULL | 2 daniel
35 54 57 NULL NULL | 2 daniel
36 64 63 NULL NULL | 2 daniel
37 59 69 NULL NULL | 2 daniel
38 67 58 NULL NULL | 2 daniel <-Spiel 38 nicht getippt(siehe unten)
39 76 60 NULL NULL | 2 daniel
40 78 77 NULL NULL | 2 daniel
41 62 65 NULL NULL | 2 daniel
diese daten (auch das nicht getippte spiel 38) möchte ich mit den folgenden daten aus der tabelle 'spieltagtippen' (hier ist ein spieler mehr, der spiel 38 getippt hat) kombinieren:
IDSpieler IDSpiel TippToreHeim TippToreGast Spieltag
--------------------------------------------------------------------------
1 39 3 3 1
1 33 9 9 1
1 35 7 7 1
1 36 6 6 1
1 40 2 2 1
1 38 4 4 1
1 37 5 5 1
1 41 1 1 1
1 34 8 8 1
2 35 3 3 1
2 34 2 2 1
2 40 8 8 1
2 36 4 4 1
2 37 5 5 1
2 41 9 9 1
2 39 7 7 1
2 33 1 1 1
Standard
Vielen Dank für die Grafik, ich hoffe sie hilft mir weiter...wenigstens mal schön übersichtlich dargestellt denke mal selber ein bisserl malen könnte helfen
[Update]
Mit einem Cross Join bekomme ich es hin, dass jeder aktive User auch mit allen Spielen eines Spieltages verbunden wird:
Code:
SELECT ID, Heim, Gast, ToreHeim, ToreGast, UserID, UserName
FROM spieltag
CROSS JOIN users
WHERE aktiv = true
AND spieltag =1
ORDER BY UserID ASC , ID ASC
LIMIT 0 , 30
das ergebnis ist folgendes:
Code:
Alle Spiele eines Spieltages |Jeder Nutzer (auswahl User 2)
---------------------------------------------------|---------------------------
ID Heim Gast ToreHeim ToreGast | UserID UserName
33 75 51 NULL NULL | 2 daniel
34 56 66 NULL NULL | 2 daniel
35 54 57 NULL NULL | 2 daniel
36 64 63 NULL NULL | 2 daniel
37 59 69 NULL NULL | 2 daniel
38 67 58 NULL NULL | 2 daniel <-Spiel 38 nicht getippt(siehe unten)
39 76 60 NULL NULL | 2 daniel
40 78 77 NULL NULL | 2 daniel
41 62 65 NULL NULL | 2 daniel
nun muss ich noch das ganze mit der dritten tabelle, den Tipps verbinden die dann eben zusätzlich dort eingebunden werden...
HIer mal die getippten Spiele zu dem Datensatz:
Code:
IDSpieler IDSpiel TippToreHeim TippToreGast Spieltag
--------------------------------------------------------------------------
1 39 3 3 1
1 33 9 9 1
1 35 7 7 1
1 36 6 6 1
1 40 2 2 1
1 38 4 4 1
1 37 5 5 1
1 41 1 1 1
1 34 8 8 1
2 35 3 3 1
2 34 2 2 1
2 40 8 8 1
2 36 4 4 1
2 37 5 5 1
2 41 9 9 1
2 39 7 7 1
2 33 1 1 1
Ich möchte nun das ergebnis oben mit diesen daten kombinieren. Die situation ist dabei folgende: Spieler 1 hat alle 9 Spiele getippt, Spieler 2 nur 8 der 9 spiele (Spiel 38 nicht).
Egal wie ich die daten kombiniere, ich bekomme es nicht hin, dass für das spiel 38 von Spieler 2 auch ein datensatz (Nämlich NULL bei den Tipps) ausgegeben wird!
danke schonmal für die Hilfe!
-
moin,
+ID (int, Primary Key)
+Heim (int, Fremdschlüssel zur TeamID einer anderen Tabelle)
+Gast (int, Fremdschlüssel zur TeamID einer anderen Tabelle)
+ToreHeim (int)
+ToreGast (int)
+spieltag (int)in der tabelle scheint ein design fehler vorzuliegen. ich würde nicht die fremdschlüssel der heim und gastmannschaft einbinden, sondern ganz direkt die namen der mannschaft. auch den namen der tabelle finde ich nicht treffend. letzlich handelt sich sich um spiele und nicht um spieltage. aber das nur am rande.
Egal wie ich die daten kombiniere, ich bekomme es nicht hin, dass für das spiel 38 von Spieler 2 auch ein datensatz (Nämlich NULL bei den Tipps) ausgegeben wird!
es gibt verschiedene möglichkeiten das zu lösen, zum beispiel mit deinem crossecheck in kombination mit mehreren korrelierten unterabfragen in der projektion oder aber mit joins und eine geschickt gewählten ON klausel. aber dazu muss erst mal ein anders problem gelöst werden. wenn du spiele in der datenbank hast, die über mehr als eine saison geht und nicht jeder user, der aktiv ist, schon bei einer älteren saison dabei war, dann brauchst du ein startdate für die user, ab wann sie mitspielen.
aber das ist alles durch den rücken in die brust geschossen. was du letzlich willst sind teilnahmen abbilden, sprich unabhängig davon, ob ein aktiver user einen tipp abgegeben hat oder nicht, hat er doch bei jedem saisonspiel eine teilnahme und diese solltest du auch persistieren. sprich ändere deinen tabellen namen von "spieltagtippen" in teilnahmen, persistiere dann für jeden aktiven spieler über die ganze saison seine teilnahmen und wenn er einen tipp abgibt, dann hat er werte in den attributen TippToreHeim und TippToreGast, ansonsten bleiben die spalten NULL. dann kannst du auch einen ganz normalen INNER JOIN über die drei tabellen machen.
Ilja
-
Hallo,
ich bin derzeit an der Erstellung einer Internetseite für unsere wöchentliches Bundesliga-Tippspiel um mich auch mal in PHP und SQL wieder reinzufuchsen...
Nun bin ich aber auf ein kleines problem gestoßen und bräuchte eure hilfe bei einem SQL-Statement, ob dies mit einem RIGHT/LEFT join möglich ist.ja, das ist mit einem LEFT JOIN möglich, mit einem RIGHT JOIN selbstverständlich auch ;-)
Ich habe 3 Tabellen mit folgenden Strukturen:
- Users (für die Spieler)
+UserID(int, Primary Key)
+UserName(varchar)
+aktiv(aktiv) -> dient für spätere Historie um auch Saisonübergreifend Tipps von Spielern beizubehalten- spieltag (für die Speicherung der wirklichen Partien und deren Ergebnisse)
+ID (int, Primary Key)
+Heim (int, Fremdschlüssel zur TeamID einer anderen Tabelle)
+Gast (int, Fremdschlüssel zur TeamID einer anderen Tabelle)
+ToreHeim (int)
+ToreGast (int)
+spieltag (int)- spieltagtippen (Hier sind die tipps der Spieler)
- IDSpieler(int, Primary Key)
- IDSpiel(int, Primary Key)
- TippToreHeim (int)
- TippToreGast (int)
- spieltag (int)
die Spalte Spieltag in der Tipp-Tabelle erscheint mir überflüssig.
Ziel ist es, dass ich Alle Spiele eines Spieltages mit sämtlichen Tipps mir darstellen lasse, auch von denen, die nicht getippt haben (deise sind auch nicht in der tabelle spieltagtippen enthalten)!
Mit dem SQL-Statement:
SELECT ID, Heim, Gast, ToreHeim, ToreGast, UserID, UserName
FROM
spieltag
CROSS JOIN users
WHERE aktiv = true
AND spieltag =1
ORDER BY UserID ASC , ID ASC> Vielen Dank für die Grafik, ich hoffe sie hilft mir weiter...wenigstens mal schön übersichtlich dargestellt denke mal selber ein bisserl malen könnte helfen > [Update] > Mit einem Cross Join bekomme ich es hin, dass jeder aktive User auch mit allen Spielen eines Spieltages verbunden wird: Vielen Dank, wenn Du Cross-Postings vermiedest oder wenigstens auch die Grafik verlinktest. > nun muss ich noch das ganze mit der dritten tabelle, den Tipps verbinden die dann eben zusätzlich dort eingebunden werden... > Egal wie ich die daten kombiniere, ich bekomme es nicht hin, dass für das spiel 38 von Spieler 2 auch ein datensatz (Nämlich NULL bei den Tipps) ausgegeben wird! Du hast es doch schon weit gebracht: Du hast die Kombination aller Mitspieler und Partien an einem bestimmten Spieltag. Diese Datensätze möchtest Du alle haben, dazu die abgegebenen Tipps, wenn vorhanden. Das ist eine typische Aufgabe für einen [OUTER JOIN](http://aktuell.de.selfhtml.org/artikel/datenbanken/joins/#leftright_join). Du hast mindestens zwei Möglichkeiten, Dein Ergebnis zu bekommen: a) über einen [Mehrfachjoin](http://aktuell.de.selfhtml.org/artikel/datenbanken/fortgeschrittene-joins/mehrfachjoin1.htm), b) mit einem Subselect. Schauen wir uns a) an: den Mehrfachjoin. Die Ausgangstabelle wird durch Deinen Cross-Join erzeugt. Damit wir einfach weiterjoinen können, schreibst Du Deine WHERE-Klausel in die Join-Bedingung um: ~~~sql SELECT ID, Heim, Gast, ToreHeim, ToreGast, UserID, UserName FROM spieltag CROSS JOIN users ON aktiv = true AND spieltag = 1sollte abgesehen von der Sortierung das gleiche Ergebnis liefern. Du sortierst eh' das Endergebnis, nicht zwischendurch.
Erweitere das Statement um den LEFT JOIN auf die Tipptabelle und verwende Tabellen-Aliasnamen um das lästige Ausschreiben der Tabellennamen etwas angenehmer zu gestalten:
SELECT st.ID, st.Heim, st.Gast, st.ToreHeim, st.ToreGast, u.UserID, u.UserName, t.TippToreHeim, t.TippToreGast FROM ( spieltag st JOIN -- auf CROSS kann man verzichten -- dann verstehen's mehr SQL-Dialekte users u ON aktiv = true AND spieltag = 1) LEFT JOIN spieltagtippen t ON u.UserID = t.IDSpieler AND st.ID = t.IDSpiel ~~~ [1] MySQL sollte schon in der Version 5.0.12 oder neuer vorliegen. Ältere MySQL-Dialekte liefern bei komplizierteren Joins gerne systematisch falsche Ergebnisse aus und ignorierten vor Version 5.0.1 Klammern, siehe [Handbuch](http://dev.mysql.com/doc/refman/5.0/en/join.html). Freundliche Grüße Vinzenz [1] Statement getestet mit MS SQL Server 2005 Express Edition.-
moin Vinz,
SELECT
ID,
Heim,
Gast,
ToreHeim,
ToreGast,
UserID,
UserName
FROM
spieltag
CROSS JOIN
users
ON
aktiv = true AND spieltag = 1ich kann mich täuschen, aber müsste hier nicht ein INNER JOIN anstelle des CROSS JOIN stehen ? > ~~~sql SELECT > st.ID, > st.Heim, > st.Gast, > st.ToreHeim, > st.ToreGast, > u.UserID, > u.UserName, > t.TippToreHeim, > t.TippToreGast > FROM ( > spieltag st > JOIN -- auf CROSS kann man verzichten > -- dann verstehen's mehr SQL-Dialekte > users u > ON > aktiv = true AND spieltag = 1) > LEFT JOIN > spieltagtippen t > ON > u.UserID = t.IDSpieler > AND > st.ID = t.IDSpiel > ~~~ [1] hmm, ich habe leider keine datenbank, um es zu testen, aber zum einen wundert mich, dass du für die unterabfrage in der FROM klausel keinen aliasnamen brauchst und die inneren alias namen außen benutzen kannst. aber scheint offenbar so zu gehen. was mich aber noch mehr verwundert, fehlt da in der LEFT JOIn Bedinung nicht nich eine bedinung, nämlich der bgleich zwischen spieltag und spieltagtippen ? Ilja-
Hallo Ilja,
SELECT
ID,
Heim,
Gast,
ToreHeim,
ToreGast,
UserID,
UserName
FROM
spieltag
CROSS JOIN
users
ON
aktiv = true AND spieltag = 1> > ich kann mich täuschen, aber müsste hier nicht ein INNER JOIN anstelle des CROSS JOIN stehen ? vermutlich. Hab' gerade kein MySQL zur Hand und MS SQL Server mag keinen CROSS JOIN. > > ~~~sql SELECT > > st.ID, > > st.Heim, > > st.Gast, > > st.ToreHeim, > > st.ToreGast, > > u.UserID, > > u.UserName, > > t.TippToreHeim, > > t.TippToreGast > > FROM ( > > spieltag st > > JOIN -- auf CROSS kann man verzichten > > -- dann verstehen's mehr SQL-Dialekte > > users u > > ON > > aktiv = true AND spieltag = 1) > > LEFT JOIN > > spieltagtippen t > > ON > > u.UserID = t.IDSpieler > > AND > > st.ID = t.IDSpiel > > ~~~ [1] > > hmm, ich habe leider keine datenbank, um es zu testen, aber zum einen wundert mich, dass du für die unterabfrage in der FROM klausel keinen aliasnamen brauchst und die inneren alias namen außen benutzen kannst. aber scheint offenbar so zu gehen. Hab' keine Unterabfragen, nur hübsch geklammerte Joins - das kann auch Oracle :-) > was mich aber noch mehr verwundert, fehlt da in der LEFT JOIn Bedinung nicht nich eine bedinung, nämlich der bgleich zwischen spieltag und spieltagtippen ? Die fand' ich wie Du redundant - und hab' sie daher weggelassen. Spieljahr hatte ich - wie Du - ebenfalls vermisst, aber mir ging's vorrangig um das Prinzip, das [Du ja erwähntest](https://forum.selfhtml.org/?t=194705&m=1302337): > > es gibt verschiedene möglichkeiten das zu lösen, zum beispiel mit deinem crossecheck in kombination mit mehreren korrelierten unterabfragen in der projektion oder aber mit joins und eine geschickt gewählten ON klausel. Für die praktische Umsetzung denke ich, dass Dein Vorschlag > > was du letzlich willst sind teilnahmen abbilden, sprich unabhängig davon, ob ein aktiver user einen tipp abgegeben hat oder nicht, hat er doch bei jedem saisonspiel eine teilnahme und diese solltest du auch persistieren. sinnvoll ist. Dabei können bei einem Einsteigen während der Saison gleich alle Teilnahme-Datensätze erzeugt werden (ob für die gesamte Runde oder nur für die zukünftigen Spiele, wäre noch zu überlegen) - und somit gäb's später nur noch hübsche einfache UPDATE- und SELECT-Anweisungen. Das dürfte zu einfacherer Anwendungslogik führen und die paar Kilobyte "unnötig" belegten Plattenplatzes bereiten mir in Zeiten der Terabyte-Platten keine Kopfschmerzen mehr. Freundliche Grüße Vinzenz-
moin Vinz,
erst einmal, ich habe lange nichts mehr von dir gelesen, habe deine beiträge schon vermisst....
vermutlich. Hab' gerade kein MySQL zur Hand und MS SQL Server mag keinen CROSS JOIN.
denke schon, ein CROSS JOIN sollte keine ON klausel haben.
Hab' keine Unterabfragen, nur hübsch geklammerte Joins - das kann auch Oracle :-)
lach, ich habe schon tomaten auf den augen, sehe nur klammern und schon denke ich an eine unterabfrage...
was mich aber noch mehr verwundert, fehlt da in der LEFT JOIn Bedinung nicht nich eine bedinung, nämlich der bgleich zwischen spieltag und spieltagtippen ?
Die fand' ich wie Du redundant - und hab' sie daher weggelassen.
naja, ich kann es wie gesagt nicht testen, aber wenn ich deinen ersten join in gedanken nachempfinde (von der ich dachte es wäre eine unterabfrage), dann wird jeder aktiver user zu jedem spiel zugeordnet (lassen wir mal den spieltag 1 aussen vor).
aber die tipps, die ein user abgibt, sind nicht nur spezifisch zum user, sondern auch zu dem jeweiligen spiel. oder habe ich da jetzt einen denkfehler ?
Ilja
-
-