✔ Fragen zu komplexeren INNER|LEFT JOINs

[Darian]

Hallo Leute,

die angehängt Grafik zeigt in wie fern die Tabellen verknüpft sind.

Ich möchte nun gerne alle movies von movie_list die die zu einem bestimmten Kunden gehören (customer_id=1) selektieren.

Und ich selektiere ggf. auch gleich den dazu gehörigen Film (source_movies) und 1 source_picture mit der höchsten 'position' .

Für movie_list also LEFT JOIN da ich das immer brauche.

Mein SELECT sieht jetzt wie folgt aus (jetzt noch eher vereinfacht):

 SELECT *
FROM movie_list
LEFT JOIN movie_pictures
INNER JOIN source_pictures ON movie_list.id = movie_pictures.movie_id
AND movie_pictures.picture_id = source_pictures.id
AND movie_list.customer_id =1
LIMIT 0 , 30

Es ist immer ein Fehler wenn ich das LEFT JOIN benutze, mit einem INNER JOIN funktioniert es zwar, gibt mir aber nicht das richtige Ergebnis.

Später würde ich ein ORDER BY movie_pictures.position LIMIT 0 , 1 benutzen um nur das oberste Bild zu selektieren.

Fehler:

#1064 - You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'LIMIT 0, 30' at line 2

Habe schon einige verschiedene Möglichkeiten probiert, komme aber nicht zum gewünschten Ergebnis.

lg und danke für Infos
Darian

 

[Yaslaw]

In deinem Fall kannst du die Tabellen auch einfach mittels WHERE verknüpfen. Wird mMn übersichtlicher.

Aber was ganz klar falsch ist, ist der * im SELECT-Teil. MySQL weiss nicht, von welcher der 3 Tabellen er alles ausgeben soll.

Ich habe hier mal ein Beispiel zusammengestellt, wie du alle Angaben von allen Tabellen kombiniert darstellen kannst:

SELECT 
    ml.*,
    sm.*,
    mp.*,
    sp.*
FROM 
    movie_list ml,
    movie_pictures mp,
    source_pictures sp,
    source_movies sm 
WHERE
    ml.id = mp.movie_id
    AND ml.id = sm.movie_id
    AND mp.picture_id = sp.id
    AND ml.customer_id =1

Je nach Datenmengen macht es sogar Sinn, die Filterung in einem Subquery abzudecken, da so nicht alle Daten kombiniert werden und erst dann auf custemer_id gefiltert wird.

SELECT 
    ml.*,
    sm.*,
    mp.*,
    sp.*
FROM 
    (SELECT * FROM movie_list WHERE customer_id =1) ml,
    movie_pictures mp,
    source_pictures sp,
    source_movies sm 
WHERE
    ml.id = mp.movie_id
    AND ml.id = sm.movie_id
    AND mp.picture_id = sp.id

 

[Darian]

Hallo und danke für die Antwort, so würde es vermutlich schon auch funktionieren.

Aber mich würde das trotzdem mit INNER und LEFT JOIN noch interessieren. Weil da liegt irgendwo das Problem.

Und wenn ich nur * benutze, funktioniert es auch, liegt also doch rein an den joins.

Wie würde das also damit aussehen?

thx und lg

 

[Yaslaw]

2 Punke die mir aufgefallen sind.
1) Die Abfrage nach dem Customer sollte in den WHERE-Teil.
2) nach ersten JOIN muss glaub die Bedinung für den ersten JOIN folgen. Erst dann kommt der zweite Join

SELECT 
    *
FROM 
    movie_list ml
    LEFT JOIN movie_pictures mp 
        ON ml.id = mp.movie_id
    INNER JOIN source_pictures sp 
        ON mp.picture_id = sp.id
WHERE
    ml.customer_id =1

PS: Das mit dem nur * funktionert glaub nur sauber, wenn die Sourcen alle eindeutige Feldnamen haben (also kein Feldname in mehreren Sourcen vorhanden)

 

[Darian]

Hallo,

danke dir, jetzt kommen wir der Sache schon näher. Deine Tips waren gut, und werde ich in Zukunft beachten.

Das SELECT sieht jetzt wie folgt aus:

SELECT * FROM movie_list
LEFT JOIN source_movies
   ON movie_list.movie=source_movies.id
LEFT JOIN movie_pictures
   ON movie_list.id = movie_pictures.movie_id
INNER JOIN source_pictures 
   ON movie_pictures.picture_id = source_pictures.id
   WHERE movie_list.customer_id=1

Problem ist jetzt nur dass ein Film der keine Bilder zugeteilt hat, auch nicht selektiert wird. obwohl ich ja eigentlich LEFT JOIN hätte.

Derzeit habe ich es noch ohne ORDER BY und LIMIT.

thx und lg
Darian

 

[Yaslaw]

Versuch mal auch dem INNER JOIN auch ein LEFT JOIN zu machen.
Da eine Stufe höher ein LEFT JOIN ist, müsstest du glaub auch von dem picture zur Source ein LEFT JOIN nehmen damit das mit den nicht vorhandenen Chields suaber läuft.

 

[Darian]

Hallo, und wieder geht es voran.

War mir nicht klar dass ich da überall ein LEFT JOIN benutze dann.

Das nächste Problem ist jetzt nur wie ich die pictures sortiere und mit LIMIT begrenze.

Gibt es da eine Möglichkeit das zwischen durch zu machen? Oder muss ich das dann in ein Subselect bauen?

Und wie kann man sich das vorstellen, läuft das immer von oben nach unten.

Ich habe früher schon erfolgreich selects benutzt wo am Ende nur ein ON mit allem stand, und wo so Begrenzungen wie customer_id auch nicht in dem WHERE standen. Es ging aber trotzdem.

thx und lg
Darian

 

[Yaslaw]

Die Bedinung im ON-Teil geht schon. Aber siehe dieses Zitat aus der MySQL-Anleitung

Im ON-Teil sollten keine Bedingungen vorhanden sein, die zur Beschränkung der Datensätze in der Ergebnismenge verwendet werden; geben Sie solche Bedingungen besser in der WHERE-Klausel an. Es gibt aber Ausnahmen zu dieser Regel.

Zum Problem.
Du kannst am Ende noch ein ORDER BY und anschliessen ein LIMIT setzen

SELECT * FROM movie_list
LEFT JOIN source_movies
   ON movie_list.movie=source_movies.id
LEFT JOIN movie_pictures
   ON movie_list.id = movie_pictures.movie_id
LEFT JOIN source_pictures 
   ON movie_pictures.picture_id = source_pictures.id
WHERE movie_list.customer_id=1
ORDER BY mySortCriteria
LIMIT 50;

 

[Darian]

Hallo,

ok das mit dem ON und WHERE werde ich mir für die Zukunft merken.

Ich will ja nur 1 Bild (falls vorhanden) für jeden Film selectieren, wenn ich das ORDER BY und LIMIT 0 , 1 am Ende mache, werden ja die filme selbst limitiert.

Ich nehme mal an es gibt keine Möglichkeit ein zwischen Ergebnis zu limitieren und zu sortieren.

Mir bleibt also nichts anderes übrig als ein Subselect zu verwenden.

thx und lg Darian

 

[Darian]

Hallo Leute,

ich habe es jetzt. Habe zwar mit dem ORDER BY und LIMIT getrickst, weil ich einfach überprüfe ob die position 10 ist, oder ob sie null ist, aber es funktioniert.

Hier nun mein Ergebnis:

SELECT movie_list.id, movie_list.name, movie_list.res_x as resolution_x, 
	movie_list.res_y as resolution_y, movie_list.slideshow, source_movies.filename as movie_name, 
	source_movies.filesize as movie_filesize, source_movies.length as movie_length,
	source_pictures.filename as picture_name, source_pictures.filesize as picture_filesize 
	FROM `movie_list` 
			LEFT JOIN source_movies 
				ON movie_list.movie=source_movies.id 
			LEFT JOIN movie_pictures
				ON movie_list.id=movie_pictures.movie_id
			LEFT JOIN source_pictures
				ON movie_list.id=movie_pictures.movie_id AND movie_pictures.picture_id=source_pictures.id 
			WHERE movie_list.customer_id=".$this->db->quote($customer)." AND (movie_pictures.position=10 OR movie_pictures.position IS NULL)"

Danke dir für die vielen Infos
lg Darian