Frage zum Query-Aufbau

[kjh54tergkjjj]

Hallo,

ich tüftle gerade an folgender Query:

Welche Personen haben sich gegenseitig am selben tag Nachrichten geschrieben (also vonemail --> anemail und anemail --> vonemail und datum ident).

create table nachricht(
id INTEGER PRIMARY KEY NOT NULL,
vonemail VARCHAR2(60) references person(email),
anemail VARCHAR2(60) references person(email),
betreff VARCHAR2(60),
datum date,
messagetext VARCHAR2(512)
);

create table person(
email VARCHAR2(60) PRIMARY KEY NOT NULL,
vorname VARCHAR2(255),
nachname VARCHAR2(255),
geburtsdatum DATE,
geschlecht VARCHAR2(1)
);

Wie geht man hierbei am besten vor?

Ich scheitere am Vergleich die Einträge von vonemail --> anemail zu finden, die man dann auf die Gegenseitigkeit vergleichen muss. :-(

Bin über jeden Tipp dankbar.

 

[ukulele]

einfach:

SELECT * FROM nachricht n1 INNER JOIN nachricht n2 ON n1.vonemail = n2.anemail AND n1.anemail = n2.vonemail AND n1.datum = n2.datum

 

[akretschmer]

Deine nachrichtentabelle mal maximal verkleinert:

test=*# select * from nachricht ;
 von | an | datum
-----+----+-------
  1 |  2 |  1
  2 |  1 |  2
  2 |  3 |  2
  1 |  2 |  2
(4 rows)

Datum hier mal nur als INT - reicht ja zum vergleichen. Was Du suchst ist (1,2), korrekt?

test=*# select distinct least(n1.von, n1.an), greatest(n1.von, n1.an) from nachricht n1 inner join nachricht n2 on (n1.datum=n2.datum) where (n1.von, n1.an) = (n2.an, n2.von);
 least | greatest
-------+----------
  1 |  2
(1 row)

sollte passen, oder?

 

[kjh54tergkjjj]

Thx.
Nun muss ich die Tabelle nachricht noch mit der Tabelle person joinen, damit ich zu den getroffenen Emailadressen die Namen bekomme - das bedeutet ja, dass diese Query der anderen darüber gegeben wird, oder?

Ah, gelöst.

select vorname, nachname from person per
where per.email in (
select nac1.vonemail from nachricht nac1 inner join nachricht nac2
on nac1.vonemail = nac2.anemail and
nac1.anemail = nac2.vonemail and
nac1.datum = nac2.datum
);

 

[akretschmer]

ja, und?

 

[ukulele]

Kommt drauf an was für Spalten du alle haben willst:

SELECT   *
FROM   nachricht n1
INNER JOIN nachricht n2
ON     n1.vonemail = n2.anemail
AND     n1.anemail = n2.vonemail
AND     n1.datum = n2.datum
LEFT JOIN person p1
ON     n1.vonemail = p1.email
LEFT JOIN person p2
ON     n2.vonemail = p2.email

 

[kjh54tergkjjj]

Wie geht man bei dieser Query am besten vor?

Für jede Person die Anzahl der Freunde ausgeben?

create table hatfreund(
email VARCHAR2(60) references person(email),
emailfreund VARCHAR2(60) references person(email),
CONSTRAINT hatfreundKEY primary key(email,emailfreund)
);

Join über die Tabellen: person / hatfreund

 

[ukulele]

Da gibts diverse Möglichkeiten:

SELECT   p.*,
     isnull(t.anzahl,0) AS anzahl
FROM   person p
LEFT JOIN (

SELECT   email,
     count(*) AS anzahl
FROM   hatfreund
GROUP BY email

     ) t
ON     p.email = t.email

 

[akretschmer]

mal gebastelt. Mit diesen Tabellen:

test=*# select * from personen ;
 id |  name   
----+----------
  1 | person 1
  2 | person 2
  3 | person 3
  4 | name 4
(4 rows)

test=*# select * from nachricht ;
 von | an | datum
-----+----+-------
  1 |  2 |  1
  2 |  1 |  2
  2 |  3 |  2
  1 |  2 |  2
  1 |  4 |   
  4 |  2 |   
  4 |  3 |   
(7 rows)

sowie PostgreSQL mit der intarray-Extension:

test=*# select pid, uniq(sort(array_remove(x1 || x2,NULL))) from (select von.id as pid, * from (select p.id, array_agg(distinct n1.an) as x1 from personen p left join nachricht n1 on p.id=n1.von group by 1) von left join (select p.id, array_agg(distinct n1.von) as x2 from personen p left join nachricht n1 on p.id=n1.an group by 1) an on von.id=an.id) bla;
 pid |  uniq   
-----+---------
  1 | {2,4}
  2 | {1,3,4}
  3 | {2,4}
  4 | {1,2,3}
(4 rows)

In der zweiten Spalte die Personen-ID's, mit denen nachrichten ausgetausch wurden, unique.