[MySQL] Verwendete Datensätze nicht anzeigen & Datensätze zählen

[ByeBye 242513]

Hallo Community,
hoffentlich ist der Titel nicht zu kompliziert gewählt, leider ist mir kein besserer eingefallen. Ich habe zwei Frage und komme zu keiner Lösung Die Ausgangssituation der Datenbank ist wie folgt:

Tabelle Buchung
ProduktID   Datum         Bezeichnung
1           1421746163    Buchung 1
1           1421746171    Buchung 2
4           1421747452    Buchung 3

Tabelle Produkt
ID    KategorieID   Bezeichnung
1     1             Produkt 1
2     1             Produkt 2
3     2             Produkt 3
4     2             Produkt 4

Tabelle Kategorie
ID    Bezeichnung
1     Kategorie 1
2     Kategorie 1

Frage 1)
Ich möchte gerne Produkte aus der Tabelle "Produkt" ausgeben, jedoch nur wenn die Produkt-ID nicht in der Tabelle "Buchung --> ProduktID" vermerkt ist. In diesem Beispiel sollte nur Produkt 2 und Produkt 3 aufgelistet werden. Mein aktueller Query sieht wie folgt aus, mit welchen Befehl muss ich dieses Erweiterung um ans Ziel zu gelangen:

$sql = 'SELECT
          p.Bezeichnung,
          b.Bezeichnung
        FROM
          Produkt AS p
        INNER JOIN
          Buchung AS b ON b.ProduktID = p.ID
        WHERE
          CONCAT(p.Bezeichnung, b.Bezeichnung) LIKE ?
        ORDER BY p.Bezeichnung ASC';
       
if($stmt = $db->prepare($sql)) {
  $stmt->bind_param('s', $suche);
  $stmt->execute();
  $stmt->bind_result($produkt_bezeichnung, $buchung_bezeichnung);
  $stmt->store_result();
  $result = $stmt->num_rows;
  while($stmt->fetch()) {
    ...
  }
  $stmt->close();
}

Frage 2)
Ich möchte die Anzahl der Buchungen (Gruppiert nach Produkt) ausgeben die im aktuellen Jahr (2015) angefallen sind. In diesem Beispiel Produkt 1 = 2x, Produkt 4 = 1x. Mein aktueller Query sieht wie folgt aus, mit welchen Befehl muss ich dieses Erweiterung um ans Ziel zu gelangen:

$sql = 'SELECT
          b.Bezeichnung,
          p.Bezeichnung,
          k.Bezeichnung
        FROM
          Buchung AS b
        INNER JOIN
          Produkt AS p ON b.ProduktID = p.ID
        INNER JOIN
          Kategorie AS k ON p.KategorieID = k.ID
        WHERE
          CONCAT(b.Bezeichnung, p.Bezeichnung, k.Bezeichnung) LIKE ?
        ORDER BY p.Bezeichnung ASC';
       
if($stmt = $db->prepare($sql)) {
  $stmt->bind_param('s', $suche);
  $stmt->execute();
  $stmt->bind_result($buchung_bezeichnung, $produkt_bezeichnung, $kategorie_bezeichnung);
  $stmt->store_result();
  $result = $stmt->num_rows;
  while($stmt->fetch()) {
    ...
  }
  $stmt->close();
}

 

[Yaslaw]

Antwort 1)
Über das folgende Query findest du deine Produkte 2 und 3. Jetzt musst du es nur noch in deine Lösung implementieren

SELECT p.*
FROM produkt AS p
    LEFT JOIN buchung AS b ON p.id = b.produkt_id
WHERE
    b.produkt_id IS NULL

Antwort 2)

SELECT
    p.id
    COUNT(*)
FROM
    produkte AS p
GROUP BY
    p.id

 

[Spyke]

wäre zu Frage 1 nicht besser?

SELECT * FROM produkt WHERE id NOT IN(SELECT produktid FROM buchung GROUP BY produktid)

ich würde jetzt von ausgehen das produktid in der tabelle buchung ein foreingkey auf die tabelle produkt ist und bei einer buchung zwingend auch ein produkt gesetzt sein muss

Edit:
ah sry habs kapiert
das IS NULL irritierte mich zuerst

 

[Yaslaw]

Es gibt viele Varianten. Ich kann nicht sagen, welche schneller ist.

 

[Spyke]

Hab scho mein Post editiert, die prüfung auf IS NULL hatte mich zuerst irritiert aber in verbindung mit LEFT JOIN passts ja.
Hatte mich eher grad gefragt ob überhaupt resultate kommen würden, aber das geheimnis liegt ja im LEFT.

 

[ByeBye 242513]

Antwort 2)

SELECT
    p.id
    COUNT(*)
FROM
    produkte AS p
GROUP BY
    p.id

Vielen Dank, Antwort 1 funktioniert wunderbar. Bei Antwort 2 komme ich allerdings nicht weiter. Es sollten nur die Buchungen gezählt werden die im aktuellen Jahr (2015) getätigt wurden.

 

[Yaslaw]

Lösung 2) ist auch nur ein Anhaltspunkt
Umgesetzt sieht das natürlich dann etwa so aus

SELECT
   p.id,
   p.bezeichnung,
   COUNT(b.id) AS anzahl
FROM
   produkt AS p,
   buchung AS b
WHERE
   p.id = b.produktid
   AND YEAR(b.datum ) = 2015
GROUP BY
   p.id,
   p.bezeichnung

 

[ByeBye 242513]

Leider erzielt der Code nicht das was ich möchte. Nach einigen Recherchen bin ich auf folgendes gestoßen. Der Code würde auch funktionieren wenn der Timestamp richtig behandelt wird, jedoch erkennt MySQL das Jahr 2015 aus dem Timestamp (1421881200) nicht. Was stimmt an dem Query nicht?

SELECT
  (CASE WHEN YEAR(b.Datum) = 2015 THEN COUNT(b.Datum) END)
FROM
  ...

 

[Yaslaw]

Das SQL macht wenig Sinn.
Zeig mal dein ganzes SQL

Deine Zahl ist auch kein Date, sondern ein UNIX_TIMESTAMP. Also das Date von PHP. Darum musst du es zuerst konvertieren

YEAR(FROM_UNIXTIME(1421881200)) = 2015

 

[ByeBye 242513]

So sieht es aktuell aus, was wäre zu verbessern?

$jahr = date('Y',time());

$sql = 'SELECT
          b.ID,
          b.Bezeichnung,
          p.Bezeichnung,
          k.Bezeichnung
          CASE WHEN YEAR(FROM_UNIXTIME(b.Datum)) = ? THEN COUNT(b.Datum) ELSE NULL END AS summe_buchungen_aktuelles_jahr
        FROM
          Buchung AS b
        INNER JOIN
          Produkt AS p ON b.ProduktID = p.ID
        INNER JOIN
          Kategorie AS k ON p.KategorieID = k.ID
        WHERE
          CONCAT(b.Bezeichnung, p.Bezeichnung, k.Bezeichnung) LIKE ? GROUP BY b.ID
        ORDER BY p.Bezeichnung ASC';
if($stmt = $db->prepare($sql)) {
  $stmt->bind_param('is', $jahr, $suche);
  $stmt->execute();
  $stmt->bind_result($buchung_id, $buchung_bezeichnung, $produkt_bezeichnung, $kategorie_bezeichnung, $summe_buchungen_aktuelles_jahr);
  $stmt->store_result();
  $result = $stmt->num_rows;

  if($result > 0) {
    while($stmt->fetch()) {
      ...
    }
  }
  $stmt->close();
}