SQL über 3 Tabellen & JOIN

eisbaersche · 26.01.2010, 12:20

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:
1) 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

2) 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)

3) 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, die Datenbank (mysql) 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!
Mit dem Statement:

Code:

Select * from spieltag , spieltagtippen where ID=IDSpiel and spieltagtippen.spieltag=1

bekomme ich ja sämmtliche Spiele die getippt wurden, allerdings nicht die, wo ein spieler nicht getippt hat und das sähe so aus:

Code:

ID 	Heim 	Gast 	ToreHeim 	ToreGast 	Spieltag 	IDSpieler 	IDSpiel 	TippToreHeim 	TippToreGast 	Spieltag
41 	62 	65 	NULL 		NULL 		1 		1 		41 		1 		1 		1
41 	62 	65 	NULL 		NULL 		1 		2 		41 		9 		9 		1

-->SPieler 1&2 haben das Spiel getippt, aber 3 nicht

Ist das möglich diese auch anzeigen zu lassen mit bspw. NULL als Tipps um zu so einem Ergebnis zu kommen?:

Code:

ID 	Heim 	Gast 	ToreHeim 	ToreGast 	Spieltag 	IDSpieler 	IDSpiel 	TippToreHeim 	TippToreGast 	Spieltag
41 	62 	65 	NULL 		NULL 		1 		1 		41 		1 		1 		1
41 	62 	65 	NULL 		NULL 		1 		2 		41 		9 		9 		1
41 	62 	65 	NULL 		NULL 		1 		3 		41 		NULL 		NULL		1

--> So wäre es schön zur weiteren Auswertung.

Danke schonmal im Voraus

mermshaus · 26.01.2010, 13:31

Irgendwie so, denke ich. Statt des Sternchens solltest du genau die gewünschten Spalten selektieren.

Falls dir das nicht weiterhilft (zum Beispiel weil es nicht klappt), würde ich mir einen SQL-Dump mit Testdaten wünschen.

Die Benennung der "Spalten" deines Schemas könnte zudem einheitlicher sein.

Code:

SELECT
    *
FROM
    `Users` u
LEFT JOIN
    `spieltagtippen` st
ON
    u.`UserID` = st.`IDSpieler`
INNER JOIN
    `spieltag` s
ON
    st.`IDSpiel` = s.`ID`
WHERE
    st.`spieltag` = 1

eisbaersche · 26.01.2010, 14:07

Hallo,

danke für die fixe antwort..leider bringt die vorgeschlagene variante von dir auch nur das ergebnis, dass die getippten spiele der user angezeigt werden, jedoch nicht die nicht getippten.

ich habe mal ein sql-dump der 3 tabellen in den anhang gefügt mit testdaten.
Am besten kann man das sicher mit spieltag 34 testen:
- es gibt 3 spieler (User)
- an Spieltag 34 ist ein Spiel eingetragen
- Das eine Spiel ist von dem User bauletta getippt worden, nicht aber von daniel und test

das ergebnis deines (und auch meiner abfragen, habs mit cross joins usw versucht aber kläglich gescheitert) sql ergibt lediglich den datensatz bauletta mit seinen tipps, aber nicht die beiden anderen (bei denen dann in den tipps NULL stehen sollte)

vielen dank schonmal für die hilfe!

mermshaus · 26.01.2010, 14:30

Zweiter Versuch:

Code:

SELECT
    *
FROM
    `users` u
LEFT JOIN
    `spieltagtippen` st
ON
    u.`UserID` = st.`IDSpieler`
LEFT JOIN
    `spieltag` s
ON
    st.`IDSpiel` = s.`ID`
WHERE
    st.`spieltag` = 34
    OR st.`spieltag` IS NULL

eisbaersche · 26.01.2010, 14:37

Das kommt dem Ziel schon seeeeehr nahe

ich habe versucht, da noch die aktive User mit reinzubekommen, aso users.aktiv=true, damit die, dienocht mehr dabei sind, auch nicht angezeigt werden. ALlerdings werden mir statt 3 (Bauletta, Daniel, test) nur 2 (Bauletta, test) angezeigt. Hier der Code:

Code:

SELECT  *
FROM `users` u
LEFT JOIN `spieltagtippen` st ON u.`UserID` = st.`IDSpieler`
LEFT JOIN `spieltag` s ON st.`IDSpiel` = s.`ID`
WHERE u.aktiv = true
AND (
st.`spieltag` =34
OR st.`spieltag` IS NULL
)

Gibt es ein Problem dort mit AND und OR in einem Where statement?

mermshaus · 26.01.2010, 15:02

Bei meiner Query in #4 war Daniel auch schon nicht mehr dabei. Der flog raus, weil er Datensätze mit anderen st.`spieltag` als 34 hatte und deshalb beim LEFT JOIN mit `spieltagtippen` keine NULL-Spalten angehängt bekam, sondern Spalten mit Spieltag 2 oder so. Die wurden dann von der WHERE-Klausel ganz am Ende allerdings eliminiert.

Code:

SELECT
    *
FROM
    `users` u
LEFT JOIN
    `spieltagtippen` st
ON
    u.`UserID` = st.`IDSpieler` AND st.`spieltag` = 34
LEFT JOIN
    `spieltag` s
ON
    st.`IDSpiel` = s.`ID`
WHERE
    u.`aktiv` = 1

eisbaersche · 26.01.2010, 15:22

hehe, wie ich sehe ist das doch etwas kompliziert

Dein letzter Post macht auch bei näherem betrachten nicht das, was ich eigentlich brauche. Denn wenn man das bspw. auf spieltag 1 anwendet, da gibt es insgesamt 9 spiele (halt durch 18 mannschaften)...
Spieler Bauletta hat alle 9 getippt -> alle 9 werden angezeigt
Spieler Daniel hat 8 getippt -> alle 8 Tipps werden angezeigt ABER das fehlende 9te nicht (das was er nicht getippt hat)
Spieler test hat gar nicht getippt -> er wird einmal angezeigt, statt 9x (für alle Spiele eben)

Muss ich da evtl. änderungen an den Tabellen vornehmen um das abzubilden oder geht es per einfachen sql vielleciht doch nicht und man muss das evtl. über PHP lösen ?

grüße

G.Schuster · 26.01.2010, 15:26

Ich hab mal bei sitepoint eine nette Hilfsgrafik für Joins gefunden und sie mal eben hochgeladen:
Kostenloser Bilder Upload Service - Gratis Bilder hochladen / uploaden ohne Anmeldung
Für dich dürften wohl Outer Joins interessant sein, um auch an NULL-Werte zu kommen.

eisbaersche · 26.01.2010, 16:09

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!

mermshaus · 26.01.2010, 19:37

Für Spieltag 1 mit den Daten aus deinem Dump scheint es so zu gehen (Formatierung schenke ich mir jetzt auch mal):

Code:

SELECT ID, Heim, Gast, ToreHeim, ToreGast, UserID, UserName, st.*
FROM `spieltag` s
CROSS JOIN users
LEFT JOIN spieltagtippen st 
ON st.IDSpiel = ID AND st.spieltag = s.spieltag AND st.IDSpieler = UserID
WHERE aktiv = true
AND s.spieltag = 1
ORDER BY UserID ASC , ID ASC

Themen-Optionen
Druckbare Version zeigen Jemanden per E-Mail auf dieses Thema hinweisen
Thema bewerten
Thema bewerten:

Ähnliche Themen
Thema	Autor	Forum	Antworten	Letzter Beitrag
[Erledigt] 3 Tabellen verbinden - Join geht nicht	Bernd-m	Datenbanken	4	20.08.2009 11:59
Persistence Framework	#Avedo	Software-Design	37	28.03.2009 17:32
[Erledigt] Join über 3 Tabellen	BlackPanther	Datenbanken	2	11.03.2009 19:56
Abfrage über drei und mehr Tabellen	querfisch	Datenbanken	3	27.03.2007 21:12
SELECT über zwei Tabellen	KingCrunch	Datenbanken	9	23.03.2007 04:01
Update über zwei Tabellen	tekknotrip	Datenbanken	2	26.02.2007 16:25
Abfrage über 3 Tabellen	madSoul	Datenbanken	5	26.06.2006 12:25
SQL ABfrage über mehrere Tabellen	Madden	Datenbanken	17	09.05.2006 21:19
Select über 2 Tabellen	MasterMind	Datenbanken	11	08.04.2006 11:43
Select über mehrere tabellen	Sclot	Datenbanken	14	24.11.2005 16:56
[Erledigt] Join mit 3 Tabellen		Datenbanken	21	16.05.2005 23:20
sql über 2 tabellen		PHP Tipps 2005	2	25.04.2005 21:26
Abfrage über 2 Tabellen / Join	agrajag	Datenbanken	2	14.09.2004 23:05
suche über 8 tabellen		PHP Tipps 2004	3	03.09.2004 12:48
Abfrage über 3 Tabellen		PHP Tipps 2004	3	31.07.2004 22:34

26.01.2010, 12:20
eisbaersche Neuer Benutzer Registriert seit: 26.01.2010 Beiträge: 6 PHP-Kenntnisse: Fortgeschritten	SQL über 3 Tabellen & JOIN 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: 1) 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 2) 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) 3) 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, die Datenbank (mysql) 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! Mit dem Statement: Code: Select * from spieltag , spieltagtippen where ID=IDSpiel and spieltagtippen.spieltag=1 bekomme ich ja sämmtliche Spiele die getippt wurden, allerdings nicht die, wo ein spieler nicht getippt hat und das sähe so aus: Code: ID Heim Gast ToreHeim ToreGast Spieltag IDSpieler IDSpiel TippToreHeim TippToreGast Spieltag 41 62 65 NULL NULL 1 1 41 1 1 1 41 62 65 NULL NULL 1 2 41 9 9 1 -->SPieler 1&2 haben das Spiel getippt, aber 3 nicht Ist das möglich diese auch anzeigen zu lassen mit bspw. NULL als Tipps um zu so einem Ergebnis zu kommen?: Code: ID Heim Gast ToreHeim ToreGast Spieltag IDSpieler IDSpiel TippToreHeim TippToreGast Spieltag 41 62 65 NULL NULL 1 1 41 1 1 1 41 62 65 NULL NULL 1 2 41 9 9 1 41 62 65 NULL NULL 1 3 41 NULL NULL 1 --> So wäre es schön zur weiteren Auswertung. Danke schonmal im Voraus


Mister Ad PHP Code Flüsterer Registriert seit: 21.08.2005 Beiträge: 4682 PHP-Kenntnisse: Fortgeschritten

26.01.2010, 13:31
mermshaus Erfahrener Benutzer Registriert seit: 14.06.2009 Beiträge: 1.723 PHP-Kenntnisse: Fortgeschritten	Irgendwie so, denke ich. Statt des Sternchens solltest du genau die gewünschten Spalten selektieren. Falls dir das nicht weiterhilft (zum Beispiel weil es nicht klappt), würde ich mir einen SQL-Dump mit Testdaten wünschen. Die Benennung der "Spalten" deines Schemas könnte zudem einheitlicher sein. Code: SELECT * FROM `Users` u LEFT JOIN `spieltagtippen` st ON u.`UserID` = st.`IDSpieler` INNER JOIN `spieltag` s ON st.`IDSpiel` = s.`ID` WHERE st.`spieltag` = 1

26.01.2010, 14:30
mermshaus Erfahrener Benutzer Registriert seit: 14.06.2009 Beiträge: 1.723 PHP-Kenntnisse: Fortgeschritten	Zweiter Versuch: Code: SELECT * FROM `users` u LEFT JOIN `spieltagtippen` st ON u.`UserID` = st.`IDSpieler` LEFT JOIN `spieltag` s ON st.`IDSpiel` = s.`ID` WHERE st.`spieltag` = 34 OR st.`spieltag` IS NULL

26.01.2010, 14:37
eisbaersche Neuer Benutzer Registriert seit: 26.01.2010 Beiträge: 6 PHP-Kenntnisse: Fortgeschritten	Das kommt dem Ziel schon seeeeehr nahe ich habe versucht, da noch die aktive User mit reinzubekommen, aso users.aktiv=true, damit die, dienocht mehr dabei sind, auch nicht angezeigt werden. ALlerdings werden mir statt 3 (Bauletta, Daniel, test) nur 2 (Bauletta, test) angezeigt. Hier der Code: Code: SELECT * FROM `users` u LEFT JOIN `spieltagtippen` st ON u.`UserID` = st.`IDSpieler` LEFT JOIN `spieltag` s ON st.`IDSpiel` = s.`ID` WHERE u.aktiv = true AND ( st.`spieltag` =34 OR st.`spieltag` IS NULL ) Gibt es ein Problem dort mit AND und OR in einem Where statement?

26.01.2010, 15:02
mermshaus Erfahrener Benutzer Registriert seit: 14.06.2009 Beiträge: 1.723 PHP-Kenntnisse: Fortgeschritten	Bei meiner Query in #4 war Daniel auch schon nicht mehr dabei. Der flog raus, weil er Datensätze mit anderen st.`spieltag` als 34 hatte und deshalb beim LEFT JOIN mit `spieltagtippen` keine NULL-Spalten angehängt bekam, sondern Spalten mit Spieltag 2 oder so. Die wurden dann von der WHERE-Klausel ganz am Ende allerdings eliminiert. Code: SELECT * FROM `users` u LEFT JOIN `spieltagtippen` st ON u.`UserID` = st.`IDSpieler` AND st.`spieltag` = 34 LEFT JOIN `spieltag` s ON st.`IDSpiel` = s.`ID` WHERE u.`aktiv` = 1

26.01.2010, 15:22
eisbaersche Neuer Benutzer Registriert seit: 26.01.2010 Beiträge: 6 PHP-Kenntnisse: Fortgeschritten	hehe, wie ich sehe ist das doch etwas kompliziert Dein letzter Post macht auch bei näherem betrachten nicht das, was ich eigentlich brauche. Denn wenn man das bspw. auf spieltag 1 anwendet, da gibt es insgesamt 9 spiele (halt durch 18 mannschaften)... Spieler Bauletta hat alle 9 getippt -> alle 9 werden angezeigt Spieler Daniel hat 8 getippt -> alle 8 Tipps werden angezeigt ABER das fehlende 9te nicht (das was er nicht getippt hat) Spieler test hat gar nicht getippt -> er wird einmal angezeigt, statt 9x (für alle Spiele eben) Muss ich da evtl. änderungen an den Tabellen vornehmen um das abzubilden oder geht es per einfachen sql vielleciht doch nicht und man muss das evtl. über PHP lösen ? grüße

26.01.2010, 15:26
G.Schuster Erfahrener Benutzer Registriert seit: 21.12.2009 Beiträge: 415 PHP-Kenntnisse: Fortgeschritten	Ich hab mal bei sitepoint eine nette Hilfsgrafik für Joins gefunden und sie mal eben hochgeladen: Kostenloser Bilder Upload Service - Gratis Bilder hochladen / uploaden ohne Anmeldung Für dich dürften wohl Outer Joins interessant sein, um auch an NULL-Werte zu kommen. __________________ actra.development - Schwabstr. 2 - 70825 Münchingen www.actra.de/velopment/ - eMail: php.de@actra.de Zend Certified Engineer for PHP5

26.01.2010, 16:09
eisbaersche Neuer Benutzer Registriert seit: 26.01.2010 Beiträge: 6 PHP-Kenntnisse: Fortgeschritten	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! Geändert von eisbaersche (26.01.2010 um 18:55 Uhr).