I have problems on quering or better numerating all items from a specified table.
Here are my tables (Third normal form):
MailTable: Id, Created, Subject
AddressTable: Id, Name
SenderTable: MailTableId, AddressTableId
RecipientTable: MailTableId, AddressTableId, State
Now i want to make a query to numerate alle senders and recipients for one mail.
My query:
select m.Id, m.Created, m.Subject, sender.Name, recipient.Name
from MailTable as m
join SenderTable as s on s.MailTableId = m.Id
join AddressTable as sender on sender.Id = s.AddressTableId
join RecipientTable as r on s.MailTableId = m.Id
join AddressTable as recipient on recipient.Id = r.AddressTableId
The result:
1 | 2008-01-01 | Test | sender1@domain.tld | recipient1@domain.tld
1 | 2008-01-01 | Test | sender1@domain.tld | recipient2@domain.tld
2 | 2008-01-02 | Test | sender3@domain.tld | recipient3@domain.tld
etc.
My target:
1 | 2008-01-01 | Test | sender1@domain.tld | recipient1@domain.tld,recipient2@domain.tld
2 | 2008-01-02 | Test | sender3@domain.tld | recipient3@domain.tld
How do I achive this, whithout using time-consuming queries?
The bad way is to lookup for every mail all recipients, but this is not the best way!
Should I compare all records and extract my desired information?
Please Help me. Thanks!
modified on Tuesday, July 22, 2008 5:09 AM