SQL Zeitraum über zwei Tabellen

[mw197]

Hallo Leute,

ich bitte um eure Hilfe. Ich habe folgendes Schema an Tabellen:

Tabelle kunden

kundenID | vorname | nachname
---------+---------+-----------
   1     |   Max   | Mustermann
   2     |  Erika  | Musterfrau

Tabelle karten

kartenID | gueltig_von | gueltig_bis | kundenID
---------+-------------+-------------+---------
    1    | 2014-01-01  | 2015-01-01  |    1
    2    | 2013-04-15  | 2014-08-01  |    2
    3    | 2014-09-01  | 2014-12-31  |    2

Dort werden also nur die Karten aufgelistet, die es gibt. Und zu den Karten, gibt es dann die eingelösten Werte eines Kunden (also wie oft einer da war). Diese Daten werden so gespeichert, so dass ich insgesamt 3 Tabellen habe:

Tabelle karten_teilnahmen

teilnahmeID | kundenID |    datum
------------+----------+------------
     1      |     1    | 2014-03-05
     2      |     2    | 2014-07-06
     3      |     2    | 2014-05-08
     4      |     1    | 2014-04-04
     5      |     2    | 2014-05-29

Soweit ist alles bestens. Ich möchte aber jetzt gerne eine Auflistung, wie oft ein Kunde pro Karte da war.
Sprich: Das Ergebnis soll so aussehen:

kundenID | kartenID | COUNT
---------+----------+-------
    1    |     1    |   3
    2    |     2    |   2
    2    |     3    |   1

Kann mir jemand sagen, wie ich das hinkriege?
Mit einem normalen LEFT JOIN komme ich ja nicht weit?
Oder habe ich nur ein Brett vor dem Kopf?

Mein bisheriger Code (klappt aber nicht):

SELECT COUNT(`teilnahmeID`) AS `summe` FROM `karten_teilnahmen`
LEFT JOIN `karten` ON `karten`.`kundenID` = `karten_teilnahmen`.`kundenID`
GROUP BY `teilnahmeID`

Hat jemand eine Idee und könnte mir dabei weiterhelfen?
Ich muss ja irgendwie mit BETWEEN arbeiten, da jeder Kunde ja auch mehrere Karten kaufen kann. Nur wie bringe ich das da mit ein?! Er muss ja anhand des Gültigkeitszeitraums die Anzahl ermitteln. Das ist eigentlich mein Problem.

Danke für Tipps und Hilfestellungen im Voraus!

 

[FrankR]

Hallo,
wenn ich das Problem richtig verstehe fehlt dir lediglich eine Einschränkung.

select      teilnahmeID, count(*) as summe
from        karten_teilnahmen a
left join   karten            b on b.kundenID = a.kundenID
where       datum between gueltig_von and gueltig_bis
group by    teilnahmeID

So sollte es funktionieren, wenn die Felder datum, gueltig_von und gueltig_bis ein Datumformat besitzen (z.B. date oder datetime). Sind die Felder jedoch vom Typ Zeichenketten (z.B. char(10) oder varchar(10)) empfiehlt es sich diese im Rahmen der Abfrage in date zu überführen (convert(date,right(datum,2) || '.' || substring(datum,6,2) || '.' || left(datum,4))).

FrankR

 

[andy_0]

In SQL kann man ein logischen Vergleich auf ein Datum ausführen. Mit "WHERE Datum >= 'DatumVon' AND Datum <= 'DatumBis'" dein BETWEEN nachbauen. Ich weiß nicht, ob BETWEEN auch auf Datumsfelder anwendbar ist.

Auf die schnelle denke ich außerdem, dass du mit dem Schlüsselwort HAVING arbeiten musst, welches nach dem GROUP BY wie ein WHERE-Clause auf Aggregate Functions ("Summenfunktionen") angewendet wird. WHERE kann nicht auf Summenfunktionen angewandt werden. Schau mal bitte, ob dich dieser Tipp weiter bringt. Wenn nicht, installiere ich bei mir mal ein SQL Server (welchen verwendest du?) und spiele selbst mal.

 

[yxcv]

SELECT
	 kunden.Vorname
	,kunden.Nachname
	,karten.kartenID
	,count(*) 		AS Fahrten
FROM kunden
INNER JOIN karten
	ON kunden.kundenID = karten.kundenID
LEFT JOIN karten_teilnahmen
	ON karten_teilnahmen.kundenID	= 	karten.KundenID
	AND karten_teilnahmen.Datum 	BETWEEN karten.gueltig_von AND karten.gueltig_bis
WHERE karten_teilnahmen.Datum 		BETWEEN '01.04.2014' AND '31.05.2014'
GROUP BY 
	 kunden.Vorname
	,kunden.Nachname
	,karten.kartenID

INNER JOIN zwischen Kunde und Karten, weil es keinen Kunden ohne Karte und keine Karte ohne Kunde gibt.
LEFT JOIN weil die Karte evtl. noch nicht genutzt wurde.

Das BETWEEN im JOIN ist nötig um nur auf zulässige Kombinationen einzugrenzen,
das BETWEEN im WHERE ist optional um die Fahren innerhalb eines gewünschten Zeitraum zu suchen.


Im Count muss kein Spalte angegeben werden. Weil das Ergebnis mit * immer das selbe ist.
Ausnahme sind Count(DISTINCT Spaltenname)

Das vorgenschlagende HAVING ist falsch.
Damit könnten z.B. die Anzahl der Fahrten gezählt (wie im Select ebenfalls mit Count) werden
und nur Personen mit mehr/weniger als X Fahren anzeigen lassen.

 

[mw197]

WOW so viele Antworten schon

Danke euch erst einmal ganz herzlich. Ich habe mir das gerade in Ruhe angesehen und sieht ganz logisch aus. Da ich gerade nicht an dem System bin, werde ich das heute Abend mal ausprobieren und dann hier berichten, wie es geklappt hat.

@andy_0: Ich verwende MySQL

Danke nochmals und bis später!

Ergänzung (6. Juli 2014)

Hallo Leute,

habe das jetzt ausprobiert und bin begeistert. Klappt wirklich wunderbar und auf Anhieb. Habe den Code von yxcv genommen. Da habe ich noch eine Frage. Die GROUP BY-Klausel. Reicht es dort nicht aus, folgendes zu schreiben anstatt "vorname", "nachname"

GROUP BY 
    kunden.kundenID
    ,karten.kartenID

Ansonsten herzlichen Dank für die prompte und super Lösung!

 

[yxcv]

Da habe ich noch eine Frage. Die GROUP BY-Klausel.
Reicht es dort nicht aus, folgendes zu schreiben ...[/B]

Die Regel für "GROUP BY" ist simpel.
Alle Spalten im "SELECT" die, keine Aggregation (Count, SUM, AVG) haben, müssen 1:1 (ohne Alias) ins GROUP BY.
Wenn du keine Vor-/Nachname anzeigen möchtest, kannst du diese im SELECT/GROUP BY entfernen.

 

[mw197]

Ah, verstehe. Merci!

Thema damit 100% erledigt! DANKE!