Abfrage mit Count = 0 liefert kein Ergebnis

[guenter47]

Hallo,
ich bin neu hier und seit Tagen mit einer Abfrage am verzweifeln.
Vielleicht kann mir jemand auf die Sprünge helfen.
Das ist meine Tabell: 'members':

mem_id ! mem_rol_id ! mem_usr_id ! mem_end (date)
1118 __!____ 2 _____!___ 221 ____! 2016-02-29
1119 __!___ 33 _____!___ 221 ____! 2015-03-21
940 ___!___ 37 _____!___ 220 ____! 9999-12-31
925 ___!____ 2 _____!___ 220 ____! 2016-02-12
926 ___!___ 20 _____!___ 220 ____! 9999-12-31
1073 __!___ 55 _____!___ 220 ____! 9999-12-31
1072 __!___ 55 _____!___ 219 ____! 9999-12-31
939 ___!___ 37 _____!___ 219 ____! 9999-12-31
924 ___!___ 20 _____!___ 219 ____! 9999-12-31
923 ___!____ 2 _____!___ 219 ____! 2015-08-23
932 ___!___ 36 _____!___ 218 ____! 9999-12-31

Zur Erklärung: Jeder User (mem_usr_id) hat eine oder mehrere eindeutige Rollen (mem_rol_id) mit einem Datum (mem_end).
Das Datumsfeld kann nur entweder '9999-12-31' (bedeutet endloses Datum) oder ein kleineres Datum (= Ende-Datum) enthalten.
Ich möchte nun alle User filtern, die in keinem Datensatz das Datum '9999-12-31' stehen haben.

Die Abfrage:
SELECT mem_usr_id, COUNT(mem_end) as c
FROM `members`
WHERE (SELECT COUNT(*) FROM members WHERE mem_end = '9999-12-31' ) = 0
Liefert immer nur ein leeres Ergebnis.

Erwarten möchte ich das Ergebnis:
mem_user_id ! c
___ 221 ____! 2
Was mach ich falsch?

Danke für Eure Hilfe,
Gruß Günter

 

[akretschmer]

Was mach ich falsch?

Quasi alles. Laß das Datumsfeld leer, wenn es kein definiertes Datum gibt. Verwende in Abfragen mit Aggregationen für ALLE Spalten entweder eine Aggregation oder eine Gruppierung. Verwende HAVING, wenn Du auf das Resultat einer Aggregation prüfen willst.

 

[ukulele]

In jedem Fall fehlt dir ein GROUP BY.

Ansonsten gibts mehrere Möglichkeiten, ich würde es mit HAVING und einem CASE lösen.

SELECT   mem_usr_id,
     count(*) AS c
FROM   members
GROUP BY mem_usr_id
HAVING sum(CASE WHEN mem_end = '9999-12-31' THEN 1 ELSE 0 END) = 0

 

[guenter47]

Danke für die schnelle Antwort.
Datumsfeld leer geht nicht - die Tabelle ist so vorgegeben.
HAVING liefert ebenfalls ein leeres Resultat.

 

[akretschmer]

ich würde das sum(case ...) in die Select-Liste setzen und dann mit Having prüfen.

 

[ukulele]

Und was liefert

SELECT   mem_usr_id,
     count(*) AS c,
     sum(CASE WHEN mem_end = '9999-12-31' THEN 1 ELSE 0 END) AS test
FROM   members
GROUP BY mem_usr_id

?

 

[guenter47]

@ukulele:
Ich könnte dich umarmen.
Danke!! Funktioniert.

 

[akretschmer]

eine bessere DB als MySQL hätte Deine erste Abfrage direkt mal schon als falsch verworfen, Du hättest so viel eher gemerkt, daß es so eben nicht geht.

 

[guenter47]

SELECT mem_usr_id,
count(*) AS c,
sum(CASE WHEN mem_end = '9999-12-31' THEN 1 ELSE 0 END) AS test
FROM members
GROUP BY mem_usr_id

Die zweite Variante funktioniert nicht, sie liefert ganz viele falsche Ergebnisse:

 

[ukulele]

Falsch wohl kaum, du hast sie nur nicht erwartet. Dein Screenshot passt auch nicht zu deinen oben geposteten Daten.

Die Spalte test zählt alle Rollen die als Enddatum 9999-12-31 haben. c zählt alle Rollen. Dein User ID 1 hat also 10 Rollen, davon haben 9 das Enddatum 9999-12-31. Mit HAVING sum(CASE WHEN mem_end = '9999-12-31' THEN 1 ELSE 0 END) = 0 zeigst du nur User an, die genau 0 Rollen mit Enddatum 9999-12-31 haben, mir scheint da gibt es keine.

 

[guenter47]

Falsch wohl kaum, du hast sie nur nicht erwartet

Naja, falsch ist immer relativ.

zeigst du nur User an, die genau 0 Rollen mit Enddatum 9999-12-31 haben, mir scheint da gibt es keine.

Das stimmt so nicht, der User 221 hat 0 Rollen mit dem Enddatum 9999-12-31, siehe meine erste Tabelle ganz oben.

Deine erste Lösung funktioniert aber einwandfrei.
Die CASE WHEN Klausel in MySQL kannte ich nicht und die hilft mir bei anderen Problemen sicher auch mal weiter.
Gruß Günter

 

[guenter47]

Dein Screenshot passt auch nicht zu deinen oben geposteten Daten.

Der ist nur nicht (bzw aufsteigend) sortiert, meine geposteten Daten sin absteigend sortiert. Die Tabelle hat ca. 3000 Datensätze.

 

[guenter47]

SELECT mem_usr_id, count(*) AS c, sum(CASE WHEN mem_end = '9999-12-31' THEN 1 ELSE 0 END) AS test FROM members GROUP BY mem_usr_id

In dieser Variante müsste m.E. noch die WHERE Bedingung: WHERE test=0 rein.

 

[Hubertus]

Ich würde es einmal mit NOT EXISTS probieren. Das geht sogar in MySQL.

 

[ukulele]

So wie ich ihn verstanden habe funktioniert es ja jetzt.

NOT EXISTS wäre natürlich möglich, erfordert aber wieder ein Subquery. Ich glaube vor allem MySQL geht damit nicht gut um, dürfte also mehr DB Zeit kosten.