Peter Brawley Wrote:
-------------------------------------------------------
> The table is not well designed for the query you
> want. Is there even a primary key?
I have a primary key. The full table is:
abs | emp | content | date | subject |id|
--------+--------+-------------+----------+---------+--+
Nickel | Lisett | bla bla bla | 4.12.1991| invite | 1|
Tom | Nickel | bla bla bla | 3.10.1991| Hey | 2|
Lisett | Nickel | bla bla bla | 2.10.1991| Hey | 3|
Nickel | Lisett | bla bla bla | 15.9.1991| Hey | 4|
Tom | Nickel | bla bla bla | 03.9.1991| Hey | 5|
Nickel | Tom | bla bla bla | 01.9.1991| Hey | 6|
where id is a primary key. How could I redesign the table so it better fits the query? I have users who can log in the website and send messages to other users and abs=sender and emp=recipient are colums that only have usernames in them.
Peter Brawley Wrote:
-------------------------------------------------------
>
>
> select abs, emp, content, date from messages m
> join (
> select distinct abs as a, emp as b from
> messages
> union
> select distinct emp as a, abs as b from
> messages
> ) p on (abs=a and emp=b) or (abs=b and emp-a)
> order by a,b,date;
>
This looks nice ans makes sense but I could not completely how the code works. There is a single "m" in line 2 behind messages and a single "p" in line 8. . When I make the mysql-query I don't get an output.
Thanks for the help anyway!
greets Nick