querying all items... the best method?

Question

5.00/5 (1 vote)

See more:

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

Posted 21-Jul-08 22:56pm

gethomast

Add a Solution

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

leoinfo · Answer 1 · 2008-07-22T02:48:00

( You are using SQL2005, right? :) )

Try this:

<br />;WITH RecipientsByMail (MailId, RecipientList) AS (<br />    select mx.Id AS MailId , <br />        (   select AddressTable.Name+','<br />            from MailTable<br />            join RecipientTable on RecipientTable.MailTableId = MailTable.Id<br />            join AddressTable   on AddressTable.Id = RecipientTable.AddressTableId<br />            WHERE MailTable.Id = mx.Id<br />            FOR XML PATH('')<br />        ) AS RecipientList <br />    from MailTable as mx<br />)<br />select m.Id, m.Created, m.Subject, sender.Name, r.RecipientList<br />from MailTable as m<br />join SenderTable as s on s.MailTableId = m.Id<br />join AddressTable as sender on sender.Id = s.AddressTableId<br />join RecipientsByMail as r on r.MailId = m.Id ;