Daten aus Tabelle Flag-abhängig holen

[Rootbob91]

Guten Morgen,

ich habe aktuell 3 Tabellen:

1. tBenutzer mit folgenden Spalten:
ID (Primary, Int, Not Null, AutoInc)

2. tMails mit:
ID (Primary, Int, Not Null, AutoInc)
+ weitere..

3. tMailGesehen mit:
ID (Primary, Int, Not Null, AutoInc)
MailID -> die ID der Email die gesehen wurde
BenutzerID -> die ID des Benutzers, der sie gesehen hat
datum -> ein automatisch befüllter timestamp, wann sie gesehen wurde

Kleine Erläuterung:
Wenn sich ein Benutzer eine Mail anschaut, dann wird in der Tabelle tMailGesehen ein Eintrag vorgenommen. Dafür werden natürlich die Infos hinterlegt, wer welche Mail wann gesehen hat.

Problemstellung:
Wie schaffe ich es nun die Mails auszulesen, die noch nicht gesehen wurden?

Mein Lösungsansatz:
SELECT * FROM tMails WHERE ID NOT IN (SELECT MailID FROM tMailGesehen) AND tMailGesehen.BenutzerID <> ?

Ich bin mir sicher, dass die obere Abfrage falsch ist, aber ich komme anderweitig auch auf keine Lösung.
Es wäre nett, wenn jemand helfen könnte.

Hatte erst überlegt alles mit Subqueries zu lösen, aber in einer isses eben doch schöner.

Vielen Dank!

 

[Rootbob91]

Oder eher so aus sicht des jeweiligen Users formuliert:
"Zeige mir alle Mails, die ich noch nicht gesehen habe."

 

[akretschmer]

mal als Richtungspeilung:

test=# create table mail(id int primary key);
CREATE TABLE
test=*# create table mail_gelesen(mail_id int references mail, ts timestamp);
CREATE TABLE
test=*# insert into mail values (1);
INSERT 0 1
test=*# insert into mail values (2);
INSERT 0 1
test=*# insert into mail_gelesen values (1, now());
INSERT 0 1

test=*# select mail.id from mail left join mail_gelesen on mail.id=mail_gelesen.mail_id where mail_gelesen.mail_id is null;
 id
----
  2
(1 row)

 

[Rootbob91]

Hey wow, danke .
Ich probier eben noch rum, wie ich dann noch benutzerspezifisch arbeiten kann, also nur die nicht gesehenen Mails des jeweiligen Users anzuzeigen, aber ich denke das bekomme ich hin. Melde mich gleich nochmal!

EDIT:
So, des sieht jetzt erstmal so aus:

SELECT * FROM nw_rundschreiben LEFT JOIN nw_gesehen ON nw_rundschreiben.id = nw_gesehen.newsletterid WHERE nw_gesehen.newsletterid IS NULL AND nw_gesehen.benutzerid<>2

Leider funktioniert das nicht. Dein oberes Snippet stellt ja alle Mails dar, die noch nicht gesehen wurden, jetzt habe ich allerdings n User miteinbezogen. Hinzugefügt würde das doch eigentlich bedeuten: "Zeige mir alle Mails an, die User 2 (im obigen Beispiel) noch nicht gesehen hat".

Oder seh ich das falsch?

 

[Rootbob91]

#noPush, wollte nur das du die Nachricht siehst, was beim Edit ja nicht passiert^^ (also die Notification).

 

[akretschmer]

test=*# select * from mail;
 id
----
  1
  2
(2 rows)

test=*# select * from mail_user ;
 id
----
  1
  3
(2 rows)

test=*# select * from mail_gelesen ;
 mail_id | mail_user |  ts
---------+-----------+----------------------------
  1 |  1 | 2015-11-06 11:58:43.320204
  1 |  3 | 2015-11-06 11:58:43.320204
  2 |  3 | 2015-11-06 11:58:43.320204
(3 rows)

test=*# select * from (select m.id as m_id, mu.id as mu_id from mail m cross join mail_user mu) foo left join mail_gelesen mg on ((foo.m_id, foo.mu_id)=(mg.mail_id,mg.mail_user)) where mg.ts is null;
 m_id | mu_id | mail_id | mail_user | ts
------+-------+---------+-----------+----
  2 |  1 |  |  |
(1 row)

Das auf einen spezifischen User zu begrenzen bekommst nun aber hin, oder?

 

[Rootbob91]

Danke!

Ich denke mal mit:

select * from (select m.id as m_id, mu.id as mu_id from mail m cross join mail_user mu) foo left join mail_gelesen mg on ((foo.m_id, foo.mu_id)=(mg.mail_id,mg.mail_user)) where mg.ts is null AND mu_id=HierDieBenutzerID

Das liefert ja dann als Ergebnis die Reihe wie du sie oben dargestellt hast ab, also:

 m_id | mu_id | mail_id | mail_user | ts
------+-------+---------+-----------+----
  2 |  1 |  |  |

Wie komme ich nun an die Daten der jeweiligen Mails?
Mit der Abfrage weiß ich nun, dass die Mail mit der ID 2 vom User mit der ID 1 nicht gesehen wurde, aber wie bekomme ich die Daten der Mails?

Muss ich dann eine zusätzliche Abfrage starten und mit OR Verknüpfen, zb.:

SELECT * FROM mails WHERE id=

Und dann mit OR die Mail-IDs aus der vorherigen Abfrage verknüpfen?

 

[akretschmer]

Wie komme ich nun an die Daten der jeweiligen Mails?

Das Konzept eines Joins kennst Du?

 

[Rootbob91]

Nicht so ganz, habe mir schon mehrere Male versucht das anzueignen, aber ich verstehe es einfach nicht.
Im Kopf habe ich, dass es 2 Tabellen praktisch zu einer macht? Aber anwenden ist dann ggf. noch schwieriger.

 

[akretschmer]

dann schau dir meine bisherigen Antworten an, da hast einige Joins. Und Google kennt das Thema auch.

 

[Rootbob91]

Ich möchte nicht unhöflich sein, aber ich sagte ja -> ich versuche schon länger das Thema zu verstehen usw, aber ich ralls nicht .
Heißt dann selbstverständlich, das ich in Eigenarbeit bereits mehrfach gesucht, gelesen und probiert habe .

Ich versuche mich natürlich weiter daran