Bedingung in WHERE-Klausel

[Auge]

Hallo

Gibt es in MySQL eine Möglichkeit, in der WHERE-Klausel eine Bedingung (IF()) zu formulieren? Ich habe eine Tabelle mit Beiträgen, die sowohl von registrierten wie von unregistrierten Benutzern stammen können. In der Tabelle der Beiträge wird für unregiestrierte Benutzer deren Angabe zu Name und Email hinterlegt. Ist der Benutzer registriert, wird dort nur dessen ID hinterlegt.

Ich möchte dem Autor eines Beitrags Nachrichten über Kommentare zu den Beiträgen zukommen lassen. Ist der betreffende Benutzer nicht registriert, ist das einfach, ich lade einfach seinen namen und die Emailadresse aus der Tabelle. Stammt ein Beitrag von einem registrierten Benutzer, sollen Daten zu diesem Benutzer stattdessen aus der Benutzertabelle geholt werden, denn dort sind sie vermerkt.

Tabelle: Beitraege
id | text  | user_id | name | email           | email_notify
 1 | bla   |       0 | Ulf  | [e-mail] |            1
 2 | blubb |       1 |      |                 |            1

Tabelle: Benutzer
id | name | email
 1 | Alf  | [e-mail]

SELECT  
t1.id,  
t1.user_id,  
t1.name,  
t1.email  
FROM Beitraege AS t1  
WHERE t1.email_notify = 1

Frage ich nur die Tabelle Beitraege ab, erhalte ich alle relevanten Beiträge, für registrierte Benutzer aber nur deren ID.

SELECT  
t1.id,  
t1.user_id,  
t1.name,  
t1.email,  
t2.name AS uname,  
t2.email AS uemail  
FROM Beitraege AS t1, Benutzer AS t2  
WHERE t1.email_notify = 1  
	AND t1.user_id = t2.id

Verknüpfe ich sie mit der Tabelle Benutzer, erhalte ich widerum nur Datensätze, die von registrierten Benutzern stammen aber nicht die der nicht registrierten Benutzer.

Wie kann ich die Abfrage mit _einem_ Query erledigen, und alle Daten, egal, ob von registrierten oder nicht registrierten Benutzern, erhalten? Ist es zudem möglich, dass Daten aus der Tabelle Benutzer die analogen Felder aus der Tabelle Beiträge überschreiben, falls sie in der Tabelle Benutzer vorhanden sind? Ich möchte auf die Art vermeiden, im Ergebnis nach "name und "uname" bzw. "email" und "uemail" unterscheiden zu müssen.

Tschö, Auge

[mrjerk]

Hallo,

Wie kann ich die Abfrage mit _einem_ Query erledigen, und alle Daten, egal, ob von registrierten oder nicht registrierten Benutzern, erhalten?

Ich denke, das müsstest Du mit einem LEFT- oder RIGHT-Join hinbekommen (je nachdem, wie herum du es macht). Der INNER/EQUI-Join, den Du benutzt, liefert nur die Datensätze, die in BEIDEN Tabellen vorkommen (Beiträge von registrierten Benutzern), wenn Du hingegen z.b. die Registrierungstabelle über einen LEFT JOIN mit der Beitragstabelle verknüpfst, bekommst Du auch die Beiträge, die keinen registrierten Benutzer haben (die entsprechenden Felder sind dann null) - ohne Gewähr :).
Guck Dir am besten mal den Selfhtml-Artikel dazu an.

Ist es zudem möglich, dass Daten aus der Tabelle Benutzer die analogen Felder aus der Tabelle >Beiträge überschreiben, falls sie in der Tabelle Benutzer vorhanden sind? Ich möchte auf die >Art vermeiden, im Ergebnis nach "name und "uname" bzw. "email" und "uemail" unterscheiden zu >müssen.

Nicht im SQL-Standard, über die Ablaufsteuerungsfunktionen in MySQL müsste es aber gehen.

Viele Grüße,
Jörg

[dedlfix]

Tach!

Ich denke, das müsstest Du mit einem LEFT- oder RIGHT-Join hinbekommen (je nachdem, wie herum du es macht). [...] - ohne Gewähr :).

Nicht so bescheiden, war doch richtig :-)

Ist es zudem möglich, dass Daten aus der Tabelle Benutzer die analogen Felder aus der Tabelle Beiträge überschreiben, falls sie in der Tabelle Benutzer vorhanden sind? Ich möchte auf die Art vermeiden, im Ergebnis nach "name und "uname" bzw. "email" und "uemail" unterscheiden zu müssen.
Nicht im SQL-Standard, über die Ablaufsteuerungsfunktionen in MySQL müsste es aber gehen.

Ergänzend: Überschreiben muss man da nichts. Man wählt über einen entsprechenden Ausdruck den einen oder anderen (Feld-)Wert aus, den man gern in der Ergebnismenge haben möchte. IFNULL() aus den Control Flow Functions wäre eine Möglichkeit. Eine zweite wäre COALESCE().

dedlfix.

[Auge]

Hallo

Danke für die Hilfe.

Wie kann ich die Abfrage mit _einem_ Query erledigen, und alle Daten, egal, ob von registrierten oder nicht registrierten Benutzern, erhalten?

Ich denke, das müsstest Du mit einem LEFT- oder RIGHT-Join hinbekommen (je nachdem, wie herum du es macht). Der INNER/EQUI-Join, den Du benutzt, liefert nur die Datensätze, die in BEIDEN Tabellen vorkommen (Beiträge von registrierten Benutzern), wenn Du hingegen z.b. die Registrierungstabelle über einen LEFT JOIN mit der Beitragstabelle verknüpfst, bekommst Du auch die Beiträge, die keinen registrierten Benutzer haben (die entsprechenden Felder sind dann null) - ohne Gewähr :).

Mit einem LEFT JOIN klappt das ganz wunderbar.

Guck Dir am besten mal den Selfhtml-Artikel dazu an.

Wer (weiter unten weiter)lesen kann ist klar im Vorteil. Den Artikel hatte ich nämlich schon offen. *grmpf*

Ist es zudem möglich, dass Daten aus der Tabelle Benutzer die analogen Felder aus der Tabelle >Beiträge überschreiben, falls sie in der Tabelle Benutzer vorhanden sind? Ich möchte auf die >Art vermeiden, im Ergebnis nach "name und "uname" bzw. "email" und "uemail" unterscheiden zu >müssen.

Nicht im SQL-Standard, über die Ablaufsteuerungsfunktionen in MySQL müsste es aber gehen.

Da im Gegensatz zum von mir vorgestellten Szenario bei einigen Einträgen der Benutzername und die Email eines registrierten Benutzers sehr wohl in der Tabelle Beitraege drinstehen [1], damit also in beiden Tabellen vorhanden sind, fallen dedlfixs Angebote IFNULL und COALESCE weg, da sie eines der beiden Felder als leer (bzw. NULL) erfordern. Mit der Funktion CASE aus der Ablaufsteuerung funktioniert das.

Der fertige Query:

SELECT  
t1.id,  
t1.user_id,  
CASE WHEN t1.user_id > 0 THEN t2.name ELSE t1.name END AS name,  
CASE WHEN t1.user_id > 0 THEN t2.email ELSE t1.email END AS email  
FROM Beitraege AS t1 LEFT JOIN Benutzer AS t2  
ON t2.id = t1.user_id  
WHERE t1.email_notify = 1

[1] Ich hatte im Eröffnungsposting behauptet, bei registrierten Benutzern wären die betreffenden Felder definitiv leer.

Tschö, Auge

[dedlfix]

Tach!

Da im Gegensatz zum von mir vorgestellten Szenario bei einigen Einträgen der Benutzername und die Email eines registrierten Benutzers sehr wohl in der Tabelle Beitraege drinstehen [1], damit also in beiden Tabellen vorhanden sind, fallen dedlfixs Angebote IFNULL und COALESCE weg, da sie eines der beiden Felder als leer (bzw. NULL) erfordern.

NULL erfordern beide nicht unbedingt. Es kommt nun drauf an, ob der Dreh- und Angelpunkt die user_id ist, oder ob du anhand der Inhalte entscheiden kannst, was genommen werden soll. Soll die registrierte Angabe vor der Beitragsangabe genommen werden, dann kann COALESCE(reg.Name, beitrag.name) die gewünschten Daten liefern. Soll primär die Beitragsangabe genommen werden, muss diese NULL sein, wenn der Nutzer registriert ist und diese Daten nehmen willst: COALESCE(beitrag.name, reg.name). Das geht so auch in dem Fall, dass eine vorhandene Beitragsangabe immer eine registrierte Angabe übertrumpfen soll.

In dem Fall mit zwei Werten ist auch IFNULL() eine Alternative zu COALESCE().

Mit der Funktion CASE aus der Ablaufsteuerung funktioniert das.

Wenn die user_id ausschlaggebend ist, kannst du IF() nehmen. Das lässt sich bei einfachen Alternativen kürzer schreiben.

dedlfix.

[Auge]

Hallo

Tach!

... Post.

Danke für die Erinnerung, da muss ich auch noch hin.

Da im Gegensatz zum von mir vorgestellten Szenario bei einigen Einträgen der Benutzername und die Email eines registrierten Benutzers sehr wohl in der Tabelle Beitraege drinstehen, damit also in beiden Tabellen vorhanden sind, fallen dedlfixs Angebote IFNULL und COALESCE weg, da sie eines der beiden Felder als leer (bzw. NULL) erfordern.

NULL erfordern beide nicht unbedingt. Es kommt nun drauf an, ob der Dreh- und Angelpunkt die user_id ist, oder ob du anhand der Inhalte entscheiden kannst, was genommen werden soll. Soll die registrierte Angabe vor der Beitragsangabe genommen werden, dann kann COALESCE(reg.Name, beitrag.name) die gewünschten Daten liefern. Soll primär die Beitragsangabe genommen werden, muss diese NULL sein, wenn der Nutzer registriert ist und diese Daten nehmen willst: COALESCE(beitrag.name, reg.name). Das geht so auch in dem Fall, dass eine vorhandene Beitragsangabe immer eine registrierte Angabe übertrumpfen soll.

Beide von dir vorgeschlagenen Wege wären gangbar.

Unter der Voraussetzung, dass für einen registrierten Benutzer Name und Email in der Tabelle Benutzer vorhanden sind, kann man die Felder vor denen der Tabelle Beitraege abfragen, wobei ein Wert != NULL heraus kommt (erfrischend, mit der Nase auf das Offensichtliche, nämlich die Umkehr der Abfrage, gestoßen zu werden). In einer der beiden Tabellen sind die gewünschten Daten definitiv hinterlegt, manchmal auch in beiden, wobei dann die Tabelle Benutzer Priorität hat.

Andererseits ist für einen registrierten Benutzer in der Tabelle Beitraege immer eine user_id > 0 eingetragen und in der Tabelle Benutzer gibt es dann auch einen passenden Datensatz. Von daher empfinde ich die Unterscheidung anhand dieses Datums als sauberer.

In dem Fall mit zwei Werten ist auch IFNULL() eine Alternative zu COALESCE().

Mit der Funktion CASE aus der Ablaufsteuerung funktioniert das.

Wenn die user_id ausschlaggebend ist, kannst du IF() nehmen. Das lässt sich bei einfachen Alternativen kürzer schreiben.

die von mir gewählte Syntax

CASE WHEN t1.user_id > 0 THEN t2.name ELSE t1.name END AS name

die von dir vorgeschlagene

IF(t1.user_id > 0, t2.name, t1.name) AS name

Korrekt so?

Wo ich gerade vor dem Posting sitze, frage ich mich, was ich als besser lesbar empfinde. Die CASE-Syntax brauche ich nur vorlesen, um sie zu verstehen. Wenn ich die IF-Syntax vorlese, ersetze ich die Kommata auch nur durch Wenn-Dann-Sonst, womit sie an der Stelle für mich gleichwertig sind. Gibt es gravierende Unterschiede in der Kompatibilität zu anderen SQL-Systemen? Ist eine der beiden Formen in Sachen Wiederverwendbarkeit in anderen Systemen vorzuziehen?

Tschö, Auge

[dedlfix]

Tach!

die von mir gewählte Syntax
CASE WHEN t1.user_id > 0 THEN t2.name ELSE t1.name END AS name
die von dir vorgeschlagene
IF(t1.user_id > 0, t2.name, t1.name) AS name
Korrekt so?

Sieht gut aus.

Gibt es gravierende Unterschiede in der Kompatibilität zu anderen SQL-Systemen? Ist eine der beiden Formen in Sachen Wiederverwendbarkeit in anderen Systemen vorzuziehen?

Sich bei Datenbankabfragen um die Wiederverwendbarkeit in anderen Systemen einen Kopf zu machen, ist meist vergebliche Liebesmüh. Erstens wegen YAGNI. Wann kommst du denn in die Verlegenheit, umziehen zu müssen? Und wenn doch, was wären denn, zweitens, Umzugsgründe, um von einem kostenlosen System auf ein anderes umzusteigen? Nur die Ausführungsgeschwindigkeit? Oder die Wartbarkeit des Systems, jenseits von SQL-Statements? Oder sind es nicht doch eher die Features, die sich dann auch und zwar teilweise gehörig im SQL-Dialekt niederschlagen? Vermutlich wird es hauptsächlich letzteres sein, und dann kommst du nicht umhin, beziehungsweise es ist sehr sinnvoll, die Statements des gesamten Projekts auf notwendige und/oder mögliche Änderungen hin zu untersuchen - inklusive eines umfangreichen Tests, dass sich die Features deiner Anwendung weiterhin wie erwartet verhalten.

Ansonsten: CASE dürfte auch anderswo sehr bekannt sein.

dedlfix.

[Auge]

Hallo

die von mir gewählte Syntax
CASE WHEN t1.user_id > 0 THEN t2.name ELSE t1.name END AS name
die von dir vorgeschlagene
IF(t1.user_id > 0, t2.name, t1.name) AS name
Korrekt so?

Sieht gut aus.

danke

Gibt es gravierende Unterschiede in der Kompatibilität zu anderen SQL-Systemen? Ist eine der beiden Formen in Sachen Wiederverwendbarkeit in anderen Systemen vorzuziehen?

Sich bei Datenbankabfragen um die Wiederverwendbarkeit in anderen Systemen einen Kopf zu machen, ist meist vergebliche Liebesmüh. ...

Darum geht es nicht. Falls Hoster nach und nach von MySQL weggehen sollten, wird wohl oft MariaDB als Alternative gewählt werden, womit an dieser Stelle keine Probleme auftauchen dürften. Ich werde aber mit großer Wahrscheinlichkeit in naher Zukunft anderweitig mit MSSQL zu tun haben. Da es nicht meine Stärke ist, mich anlasslos tiefer mit Systemen zu beschäftigen, die ich momentan nicht nutze, sind Nebenbeiinfos sehr willkommen.

Dazu dient die Lektüre von Artikeln und (hier) Threads, so sie mir über den Weg laufen, und gelegentlich auch eine eigene Nachfrage. Die gewonnenen Informationen werden abgespeichert und ergeben, wenn nötig, einen möglichst breiten Einstieg.

Ansonsten: CASE dürfte auch anderswo sehr bekannt sein.

nochmal danke

Tschö, Auge

[bubble]

Der Thread ist zwar schon relativ alt und gelöst, möchte aber trotzdem noch meinen Senf zur Struktur abgeben:

Tabelle: Beitraege
id | text  | user_id | name | email           | email_notify
1 | bla   |       0 | Ulf  | [e-mail] |            1
2 | blubb |       1 |      |                 |            1

Tabelle: Benutzer
id | name | email
1 | Alf  | [e-mail]

Ich find die Struktur unpassend, da sie relativ viel Redundanz beinhaltet. Wenn sowieso ein Feld für die User-ID existiert, sollte man dann nicht auch alle User-Informationen in die Benutzer-Tabelle stopfen und zwar nur dort?

Die Unterscheidung zwischen registriertem und nicht-registriertem Benutzer könnte man in ein eigenes Feld in der Benutzer-Tabelle auslagern, email_notify auch (wenn es nur global sein soll), wenn man einen globalen Standardwert dafür haben will, aber auch für jeden Beitrag entscheiden könen möchte halt in beiden.

Also Beitraege wäre dann
id, text, user_id (, email_notify)

und Benutzer
id, registered, name, email (, email_notify)

Vorteil: Einfach gehaltenes Query, dadurch besser wartbar.

Eventuell weiterer Vorteil: Falls sich ein nicht-registrierter Benutzer immer unter gleichen Namen und gleicher E-Mailadresse meldet und sich dann doch zur Registrierung entscheidet, wäre eine Zuordnung alter Beiträge möglich.

Nachteil: Wenn der Tabelle Benutzer weitere Informationen für registrierte Benutzer hinzukommen, hat man für unregistrierte Benutzer entweder ein haufen NULL-Felder, oder aber man muss eine extra Tabelle für Benutzerinformationen erzeugen.

(Hierbei interessiert mich auch die Meinung der Gurus)

MfG
bubble

[Auge]

Hallo

Der Thread ist zwar schon relativ alt und gelöst, möchte aber trotzdem noch meinen Senf zur Struktur abgeben:

Tabelle: Beitraege
id | text  | user_id | name | email           | email_notify
1 | bla   |       0 | Ulf  | [e-mail] |            1
2 | blubb |       1 |      |                 |            1

Tabelle: Benutzer
id | name | email
1 | Alf  | [e-mail]

Ich find die Struktur unpassend, da sie relativ viel Redundanz beinhaltet.

Kuzer historischer Ausflug: Die Struktur der hier Beitraege genannten Tabelle stammt aus einer Zeit, als in diesem Skript registrierte Benutzer noch nicht vorgesehen waren. Als der damalige Entwickler sich entschied, diese Funktion bereitzustellen, fügte er das Feld user_id und die Tabelle für die Daten der registrierten Benutzer hinzu. Auf diese Art blieben bei einem Update des Skripts die bisherigen Einträge unangetastet.

Die Struktur blieb bis heute so, wie sie damals festgelegt wurde. Ich habe auch schon darüber nachgedacht, das zu entzerren, habe dafür aber noch keine vernünftige Lösung gefunden.

Wenn sowieso ein Feld für die User-ID existiert, sollte man dann nicht auch alle User-Informationen in die Benutzer-Tabelle stopfen und zwar nur dort?

Grundsätzlich richtig, aber ...

Die Unterscheidung zwischen registriertem und nicht-registriertem Benutzer könnte man in ein eigenes Feld in der Benutzer-Tabelle auslagern, ...

... im Falle eines wiederholt postenden nicht registrierten Nutzers heißt das, dass das Skript ihn an einem weiteren Datum (z.B. einem Cookie oder der Emailadresse [1]) wiedererkennen können muss. Wenn jemand mehrfach, aber mit großem zeitlichen Abstand postet, ist es durchaus möglich, dass der Keks abgelaufen ist oder die Emailadresse gewechselt wurde. Dann passiert, dass er mehrfach in der Tabelle für die Benutzerdaten auftaucht. Das würde diese Tabelle erstens unnötig aufblähen. Zweitens kann man davon ausgehen, dass es mehrere nicht registrierte Benutzer gibt, die unter dem gleichen Namen posten. Wie soll das Skript diese dann (für eine eventuelle spätere Zuordnung der Postings zu einem nun registrierten Benutzer) unterscheiden bzw. die Postings mehrerer eingetragener Benutzer, die zum selben Poster gehören, zusammenfassen?

... email_notify auch (wenn es nur global sein soll), wenn man einen globalen Standardwert dafür haben will, aber auch für jeden Beitrag entscheiden könen möchte halt in beiden.

Persönliche Meinung: eine globale Einstellung zur Benachrichtigung bei jedem neuen Eintrag ist mMn falsch. Bei einer Instanz mit viel Betrieb kann das in spamähnliche Zustände ausarten. Dafür ist mMn ein RSS-Feed besser geeignet und der ist vorhanden.

Im Skript ist die Benachrichtigung per Email tatsächlich folgendermaßen geregelt. Klassisch ist die Einstellung, dass man sich über Antworten auf das eigene Posting benachrichtigen lassen kann [2]. Zudem kann man einzelne Threads abonnieren, wobei dann bei einer Antwort auf irgendeinen Beitrag im Thread eine Benachrichtigung geschickt wird.

Also Beitraege wäre dann
id, text, user_id (, email_notify)

und Benutzer
id, registered, name, email (, email_notify)

Vorteil: Einfach gehaltenes Query, dadurch besser wartbar.

Eventuell weiterer Vorteil: Falls sich ein nicht-registrierter Benutzer immer unter gleichen Namen und gleicher E-Mailadresse meldet und sich dann doch zur Registrierung entscheidet, wäre eine Zuordnung alter Beiträge möglich.

Meine Bedenken dazu stehen ja oben. Die Idee ist grundsätzlich nicht schlecht, ich wüsste aber keinen sicheren Weg das umzusetzen.

Nachteil: Wenn der Tabelle Benutzer weitere Informationen für registrierte Benutzer hinzukommen, hat man für unregistrierte Benutzer entweder ein haufen NULL-Felder, oder aber man muss eine extra Tabelle für Benutzerinformationen erzeugen.

An der Stelle wäre wohl die weitere Teilung der Tabellen der bessere Weg.

(Hierbei interessiert mich auch die Meinung der Gurus)

[1] Momantan ist es so, dass die Angabe einer Emailadresse nur dann nötig ist, wenn man über Antworten benachrichtigt werden will.
[2] so, wie hier in der Tabellenstruktur abzulesen

Tschö, Auge