Join Erweiterung

[Postaria]

Hallo Community,

ich denke ihr könnt mir sicher dabei helfen eine Vorhandene Query zu optimieren.

Ich habe 2 Tabellen:

[songs]
-> id (int)
-> MD5 (varchar)
-> Name (varchar)
-> deleted (datetime)
: INDEX ( id, MD5 )

[report]
-> id (int)
-> played (datetime)
-> songmd5 (varchar)
: INDEX ( id, songmd5 )

In der songs Tabelle werden Song abgelegt oder es können songs gelöscht werden.
Dabei wird der Datensatz nicht gelöscht sondern lediglich das Feld deleted auf (NOW) aktualisiert.
Standardwert von deleted ist '0000-00-00 00:00:00'.
Das heißt es können mehrere Datensätze mit dem selben MD5 in der Tabelle existieren jedoch nur einer mit dem deleted Wert '0000-00-00 00:00:00'.

Nach einem abspielen des Songs wird in die Tabelle report ein Datensatz eingefügt mit dem md5 namen des Songs.

Ich möchte nun in einer Abfrage eine Liste erhalten welche songs in einem bestimmten Zeitraum abgespielt wurden, wobei das Feld songmd5 den zugehörigen Namen aus der Tabelle songs anzeigen soll.

Das klappt auch soweit mit meinem Query:

SELECT played, Name AS File 
From
  reports 
left join songs on reports.songmd5 = songs.MD5  
where `played` >= '2009-01-27 00:00:00' AND 
      `played` <= '2009-01-27 15:00:00'

Die Abfrage zeigt mir jedoch bei doppelten MD5 aus der Tabelle songs doppelte Ergebnisse an.

Ich möchte jedoch das mir nur der Name aus der Tabelle songs angezeigt wird der zum Zeitpunkt `played` gerade aktuell war.


Hier mal ein Beispiel und das Ergebniss mit nachfolgendem gewünschten Ergebniss:

[songs]
{id} {MD5} {Name} {deleted}
1 | 77266637887 | Song1 | 2009-01-27 04:00:00
2 | 77266637887 | Song2 | 2009-01-27 06:00:00
3 | 77266637887 | Song3 | 0000-00-00 00:00:00
4 | 55511155554 | Song7 | 0000-00-00 00:00:00
5 | 88888888888 | Song9 | 0000-00-00 00:00:00

[report]
{id} {played} {songmd5}
1 | 2009-01-27 03:00:00 | 77266637887
2 | 2009-01-27 04:00:00 | 55511155554
3 | 2009-01-27 05:00:00 | 77266637887
4 | 2009-01-27 07:00:00 | 77266637887
5 | 2009-01-27 08:00:00 | 55511155554
6 | 2009-01-27 08:00:00 | 88888888888
7 | 2009-01-27 09:00:00 | 98776565564

Abfrage:

SELECT played, Name AS File 
From
  reports 
left join songs on reports.songmd5 = songs.MD5  
where `played` >= '2009-01-27 00:00:00' AND 
      `played` <= '2009-01-27 15:00:00'

Ergebniss:

2009-01-27 03:00:00 | Song1
2009-01-27 03:00:00 | Song2
2009-01-27 03:00:00 | Song3
2009-01-27 04:00:00 | Song7
2009-01-27 05:00:00 | Song1
2009-01-27 05:00:00 | Song2
2009-01-27 05:00:00 | Song3
2009-01-27 07:00:00 | Song1
2009-01-27 07:00:00 | Song2
2009-01-27 07:00:00 | Song3
2009-01-27 08:00:00 | Song7  
2009-01-27 08:00:00 | Song9  
2009-01-27 09:00:00 | NULL

Gewünschtes Ergebniss:

2009-01-27 03:00:00 | Song1
2009-01-27 04:00:00 | Song7
2009-01-27 05:00:00 | Song2
2009-01-27 07:00:00 | Song3
2009-01-27 08:00:00 | Song7  
2009-01-27 08:00:00 | Song9  
2009-01-27 09:00:00 | NULL

Das Problem besteht also darin das die Abfrage nicht nachprüft welcher song zu dem Zeitpunkt aktuell war.
Ich habe mehrere Versuche gestartet um die doppelten Einträge zu Filtern jedoch ohne nennens werte Ergebnisse.

Ich hoffe ich habe alles soweit richtig und verständlich geschildert.
Wenn nicht dann fragt mich bitte.

Ich hoffe ihr könnt mir dabei helfen.


Mit freundlichen Grüßen

Postaria

[Kropff]

Wenn ich richtig verstanden habe, dann

GROUP BY Name 

Peter

[Postaria]

Ne ein Group By gruppiert mir sämmtliche Name Felder.
Ich habe dann nicht das gewünschte Ergebniss :

2009-01-27 03:00:00 | Song1
2009-01-27 03:00:00 | Song2
2009-01-27 03:00:00 | Song3
2009-01-27 04:00:00 | Song7 
2009-01-27 08:00:00 | Song9  
2009-01-27 09:00:00 | NULL

Irgendwie muss ich die Abfrage so anpassen das er innerhalb des Joins prüft welcher Name zum Zeitpunkt des reports gerade aktuell war.

Sprich:

[report]
3 | 2009-01-27 05:00:00 | 77266637887

[songs]
1 | 77266637887 | Song1 | 2009-01-27 04:00:00
2 | 77266637887 | Song2 | 2009-01-27 06:00:00
3 | 77266637887 | Song3 | 0000-00-00 00:00:00

Hatte zum Zeitpunkt 5 Uhr den den Namen Song2, da Song1 um 4 Uhr gelöscht wurde ein neuer Song mit dem selbn md5 sagen wir um 4:19 Uhr in der songs Tabelle angelegt war und erst um 6 Uhr gelöscht wurde.


Bei meiner jetzigen Abfrage würde ich alle drei namen des MD5 als ergebniss kriegen. ich brauche aber nur den Namen der zum Zeitpung des reports existent war.


Mit freundlichen Grüßen

Postaria

[Kropff]

Ich habe mir das jetzt dreimal durchgelesen und noch immer nicht ganz verstanden. Darum begreife ich auch den Sinn nicht ganz. Ich glaube langsam, dass du einen Designfehler begangen hast. Daher mal ein paar Fragen:

Was bezweckst du mit dem md5-Hash?
Kann man die Songs umbenennen? Und diese Aussage stimmt ja wohl vorne und hinten nicht:

[songs]
1 | 77266637887 | Song1 | 2009-01-27 04:00:00
2 | 77266637887 | Song2 | 2009-01-27 06:00:00
3 | 77266637887 | Song3 | 0000-00-00 00:00:00

Hatte zum Zeitpunkt 5 Uhr den den Namen Song2, da Song1 um 4 Uhr gelöscht wurde ein neuer Song mit dem selbn md5 sagen wir um 4:19 Uhr in der songs Tabelle angelegt war und erst um 6 Uhr gelöscht wurde.

Laut deiner Angabe wurde Song3 gelöscht und nicht Song1. Bitte erklär doch mal, was du genau vorhast, vielleicht verstehe ich es dann.

Peter

[AmicaNoctis]

Der Datumswert 0000-00-00 00:00:00 steht für aktuell aktiv, also noch nicht gelöscht. Song3 gab es also um 5 noch gar nicht.

Also ich hab's verstanden und das Beispiel ergibt auch Sinn, nur hatte ich noch keine Zeit das auszuknobeln. Aber grob gesagt läuft es auf einen Inner Join und einen weiteren Self Left Join hinaus, soweit war ich schon. Vielleicht komme ich heute Nacht noch mal dazu, mir das genauer anzusehen.

Gruß,

Amica

[Kropff]

Der Datumswert 0000-00-00 00:00:00 steht für aktuell aktiv, also noch nicht gelöscht. Song3 gab es also um 5 noch gar nicht.

So würde es tatsächlich Sinn ergeben.

Vielleicht komme ich heute Nacht noch mal dazu, mir das genauer anzusehen.

OffTopic:

---

Dann mach mal, ich liege in zwei Stunden im Bett

---

Peter

[Postaria]

richtig Peter und Amica,

mit den Join kombinationen fuchtel ich auch schon herrum nur kriege ich die Abfrage nicht so hin.

Wüsste denn jemand wie so eine kombination aussehen könnte ?

[Postaria]

ich komme da immernoch nicht weiter egal ob ich da mit IF Abfragen innerhalb der Query arbeite oder nicht irgendwie werden mir entweder doppelte Ergebnisse ausgegeben oder garkeine.

Ich weiß keinen Rat mehr...

[AmicaNoctis]

select
    * -- bitte selbst das gewünschte auswählen
from report as r
left join songs as s1 on s1.md5 = r.songmd5
	and (s1.deleted = 0 or s1.deleted > r.played)
left join songs as s2 on s2.md5 = s1.md5
	and s2.deleted != 0
	and s2.deleted > r.played
	and (s1.deleted = 0 or s2.deleted < s1.deleted)
where s2.id is null;

Bin nicht eher dazu gekommen.

Gruß,

Amica

[Postaria]

Ich fass es nicht endlich.

Es geht ,
ich wär da mit Sicherheit nie im Leben drauf gekommen.

Ich Danke dir.

[AmicaNoctis]

Ich Danke dir.

Zu diesem Thema gibt es so eine Zeile in meiner Signatur... Wollte ich dir nur mal erzählen... Ich will ja nicht betteln oder sowas...