SQL LEFT JOIN liefert unpassendes Ergebnis

[te one]

Guten Abend,

möchte mir eine kleine Datenbank mit Meldungen aller möglichen Skripte aufbauen um diese dann per Handyapp abfragen zu können.
Soll nur eine Minimallösung werden, weshalb es lediglich 2 Tabellen gibt:

Grobkonzept:

Tabelle messages:
- ID (Primary)
- Type (Key für andere Tabelle, quasi Priorität/Meldungsart)
- Description (Beschreibung des Eintrags)
- Checked (Boolean, wenn Checked wurde Meldung bestätigt)


Tabelle types:
- ID
- Name (z.B. Warnung, Fehler, Info)
- Color (z.B. für Fehler "red" bzw. HTML-Notation)

Nun möchte ich eine Übersicht der Anzahl (count) offener Meldungen (Checked IS FALSE).
Da ich die Daten dann per JSON ans Android bringen möchte hätte ich gerne immer alle types.ID geliefert auch wenn aktuell der count dieser 0 ist.

Meine Versuche:
1.

SELECT types.ID,count(*),color FROM types LEFT JOIN messages ON (types.ID = messages.Type) WHERE Checked IS FALSE GROUP BY Type ORDER BY Type DESC

--> Verschluckt types mit count = 0

2.

SELECT types.ID,count(*),Color FROM types LEFT JOIN (SELECT * FROM messages WHERE Checked IS FALSE) AS messages ON (types.ID = messages.Type) GROUP BY Type ORDER BY types.ID DESC

--> Zählt auch die Einträge mit Checked IS TRUE mit

Läuft heute irgendwie nicht so... Schon lange nicht mehr programmiert

 

[Valeria]

SELECT types.ID,count(messages.id),color FROM types LEFT OUTER JOIN messages ON (types.ID = messages.Type) WHERE Checked IS FALSE GROUP BY Type ORDER BY Type DESC

Probier mal das.
Links äußerer join damit du die linke tabelle auch ohne rechten joinpartner hast, aber du zählst nur was, wenn rechts was existiert.

P.S.: ups "left join" is ja nur ne abkürzung für "left outer join"
Dann würd ich vllt ein konstrukt mit UNION versuchen^^

 

[Faust2011]

Warum teilst Du das denn auf 2 Tabellen auf? Ist das wirklich sinnvoll? In der types Tabelle hinterlegst Du, wie die Anzeige des Datensatzes erfolgt (z. B. 'Farbe für id x soll rot sein'). Das kann man zwar so machen, allerdings ist das eigentlich ein Frontend-Problem und hat in einer DB nichts zu suchen.

 

[te one]

Dann würd ich vllt ein konstrukt mit UNION versuchen^^

Vorschlag?


Die Typen sind ausgelagert, da ich das später noch erweitern möchte (verschiedene Anwendungen schreiben in die Message-Tabelle). So kann ich da relativ simpel zusätzliche Typen einfügen. Anhand der Typen-ID werden dann z.B. die mit der höchsten ID als erstes angezeigt.

Das muss sich doch simpel lösen lassen - dafür gibt es Datenbanken...

 

[mambokurt]

Guten Abend,

SELECT types.ID,count(*),color FROM types LEFT JOIN messages ON (types.ID = messages.Type) WHERE Checked IS FALSE GROUP BY Type ORDER BY Type DESC

--> Verschluckt types mit count = 0

Klar verschluckt das Typen ohne Messages, du prüfst ja explizit auf checked = false, also kriegst du nur Sätze wo checked false ist und keine wo cheked NULL ist (also keine Message zum Type da ist). Probiers mal mit WHERE (Checked IS FALSE OR checked IS NULL), dann müsste er auch Typen ohne Message dazu liefern.
Davon abgesehen gäbe es noch die 2. Option das einfach mit 2 getrennten Queries abzuholen und in 2 Arrays zu stopfen, ob das Sinn macht musst du selber wissen, kommt drauf an wie du die Daten dann weiterverarbeitest....

 

[te one]

Klar verschluckt das Typen ohne Messages, du prüfst ja explizit auf checked = false, also kriegst du nur Sätze wo checked false ist und keine wo cheked NULL ist [...] Probiers mal mit WHERE (Checked IS FALSE OR checked IS NULL), dann müsste er auch Typen ohne Message dazu liefern.

Hilft nicht.
Um Probleme mit boolean zu umgehen habe ich zur Sicherheit mal einen int aus Checked gemacht.
Folgende Abfrage liefert ebenfalls nicht alle Types zurück:

SELECT messages.Type, COUNT(*)
FROM types LEFT JOIN messages ON types.ID = messages.Type
Where Checked<>1 -- das hier sollte ja dann die mit Checked = 0 und Checked IS NULL liefern
GROUP BY messages.Type

--> Types ohne eine Message, die Checked=0 ist werden immer noch nicht angezeigt.

 

[fhtagn]

Where Checked<>1 -- das hier sollte ja dann die mit Checked = 0 und Checked IS NULL liefern

Eben nicht. Auf NULL kann man nur mit IS NULL oder IS NOT NULL prüfen.

Edit: außerdem, wie schon gesagt wurde, schränken die WHERE- und GROUP BY-Klauseln die rechte Seite auf Werte ein, die vorhanden sein müssen. Dadurch wird aus dem LEFT JOIN effektiv ein INNER JOIN.

 

[mambokurt]

Where Checked<>1 -- das hier sollte ja dann die mit Checked = 0 und Checked IS NULL liefern
[/CODE]

Nö, Vergleiche mit Null sind (fast) immer false, wenn du Null abfangen willst nutze IS NULL, sonst fliegt es dir um die Ohren. Selbst wenn du mal einen Fall haben solltest, wo du mit einem Vergleich auf Null weiterkommst, solltest du zusehen dass du IS NULL verwendest, das ist einfach gute Praxis.
Null != false gibt false, Null = false gibt false, sogar Null = Null gibt false. Da haste Lektüre: http://en.wikipedia.org/wiki/Null_(SQL).

Kann man sich ganz einfach merken: Null heisst quasi unbekannt, unbekannt = false kann sein, muss aber nicht, also zur Sicherheit false. Unbekannt = unbekannt macht auch keinen Sinn, kann das selbe sein, muss nicht.

 

[te one]

Danke für die gute Beschreibung, klappt aber auch mit WHERE Checked IS FALSE OR Checked IS NULL nicht.

Zusammenfassung:
Ich habe folgendes Message-Daten:
- ID 1, Type 1, Checked 0
- ID 2, Type 1, Checked 0
- ID 3, Type 2, Checked 0
- ID 4, Type 3, Checked 1

Sollte liefern:
- Type 1, Count 2
- Type 2, Count 1
- Type 3, Count 0 <-- Das hier fehlt immer

Edit: außerdem, wie schon gesagt wurde, schränken die WHERE- und GROUP BY-Klauseln die rechte Seite auf Werte ein, die vorhanden sein müssen. Dadurch wird aus dem LEFT JOIN effektiv ein INNER JOIN.

Genau das wird es sein - sobald ich ein WHERE definiere habe ich nicht mehr alle Einträge aus der linken Tabelle.

Also doch in Richtung Subquery? Wobei diese hier auch für Type 3 einen Count von 1 anzeigt...

SELECT types.ID,count(*),Color FROM types LEFT JOIN (SELECT * FROM messages WHERE Checked IS FALSE) AS messages ON (types.ID = messages.Type) GROUP BY Type ORDER BY types.ID DESC

 

[mambokurt]

Versuchs mal mit select count(types.id), wenn das nicht klappt poste nochmal die Tabellen inklusive Daten und ich bau das nach(theoretisch sollte das gehen, aber das ist wahrscheinich wieder so ein Fall wo der Standard nicht eindeutig war und jedes DBMS eigene Wege geht). Du Nutzt MySQL?

 

[AlbertLast]

Moin,

ich hab mal auf basis der Informationen im Thread ein Test umgebung hoch gezogen:
http://sqlfiddle.com/#!9/8028a/11/0

Es scheint mir das ich mit der Abfrage,
dem gewüschten Ergebnis des Threaderstller nah komme:

select a.id, count(b.type), a.color
from types a
left join messages b on a.id = b.type and b.checked = false
group by a.id, a.color

Type Anzahl
1 ---- 2
2 ---- 1
3 ---- 0

 

[Faust2011]

Ein wenig off-topic: Ich wußte gar nicht, dass es Fiddles auch für SQL gibt. Kannte bisher nur diverse JS-Seiten (jsbin, jsfiddle, ...). Danke (für den ungewollten Hinweis)

 

[te one]

Versuchs mal mit select count(types.id)

Das war es leider auch nicht.

Es scheint mir das ich mit der Abfrage,
dem gewüschten Ergebnis des Threaderstller nah komme:

select a.id, count(b.type), a.color
from types a
left join messages b on a.id = b.type and b.checked = false
group by a.id, a.color

Type Anzahl
1 ---- 2
2 ---- 1
3 ---- 0

Genau das ist die Lösung (count(b.type))! War mir garnicht bewusst, dass ich die JOIN-Bedingungen noch weiter ausbauen kann ohne ein WHERE zu nutzen.
SQLFIDDLE ist ja ne tolle Sache, bei zukünftigen Problemen werde ich dort eine Testumgebung aufbauen.

Vielen Dank für die Lösung dieses Problems Wieder was gelernt