querying all items... the best method? [modified]
-
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
-
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
shouteye wrote:
whithout using time-consuming queries?
You can't You are attempting to concat multiple records into 1, it will take at least 2 queries to achieve this.
Never underestimate the power of human stupidity RAH
-
shouteye wrote:
whithout using time-consuming queries?
You can't You are attempting to concat multiple records into 1, it will take at least 2 queries to achieve this.
Never underestimate the power of human stupidity RAH
Thank you! It's a major problem, or my database design? I've decided to use this but limiting to 100 records select top(100) m.Id, m.Created, m.Subject, ( SELECT ai.Name + ';' AS [text()] FROM SenderTable as s JOIN AddressTable AS at ON at.Id = s.AddressTableId WHERE s.MailTableId = m.Id FOR XML PATH('') ) as senders, ( SELECT ai.Name + ';' AS [text()] FROM RecipientTable as r JOIN AddressTable AS ai ON ai.Id = r.AddressTableId WHERE r.MailTableId = m.Id FOR XML PATH('') ) AS recipients FROM MailTable AS m Is this method better as using cursors?
modified on Tuesday, July 22, 2008 8:24 AM
-
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
( You are using SQL2005, right? :) ) Try this:
;WITH RecipientsByMail (MailId, RecipientList) AS (
select mx.Id AS MailId ,
( select AddressTable.Name+','
from MailTable
join RecipientTable on RecipientTable.MailTableId = MailTable.Id
join AddressTable on AddressTable.Id = RecipientTable.AddressTableId
WHERE MailTable.Id = mx.Id
FOR XML PATH('')
) AS RecipientList
from MailTable as mx
)
select m.Id, m.Created, m.Subject, sender.Name, r.RecipientList
from MailTable as m
join SenderTable as s on s.MailTableId = m.Id
join AddressTable as sender on sender.Id = s.AddressTableId
join RecipientsByMail as r on r.MailId = m.Id ;Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
-
( You are using SQL2005, right? :) ) Try this:
;WITH RecipientsByMail (MailId, RecipientList) AS (
select mx.Id AS MailId ,
( select AddressTable.Name+','
from MailTable
join RecipientTable on RecipientTable.MailTableId = MailTable.Id
join AddressTable on AddressTable.Id = RecipientTable.AddressTableId
WHERE MailTable.Id = mx.Id
FOR XML PATH('')
) AS RecipientList
from MailTable as mx
)
select m.Id, m.Created, m.Subject, sender.Name, r.RecipientList
from MailTable as m
join SenderTable as s on s.MailTableId = m.Id
join AddressTable as sender on sender.Id = s.AddressTableId
join RecipientsByMail as r on r.MailId = m.Id ;Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
Cute - you may be able to drop the xml, I often concat fields into CSV strings as variables, I had not thought of using a subselect. I will have to play with htis in the morning.
Never underestimate the power of human stupidity RAH
-
Cute - you may be able to drop the xml, I often concat fields into CSV strings as variables, I had not thought of using a subselect. I will have to play with htis in the morning.
Never underestimate the power of human stupidity RAH
I wonder why everyone runs away from XML... :) Let me know if you find a more elegant way. Maybe you have also time to run some performance tests ;)
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
-
I wonder why everyone runs away from XML... :) Let me know if you find a more elegant way. Maybe you have also time to run some performance tests ;)
Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
I don't object to xml - just the removal/simplification of any script. I ran across an article using XML to pass recordsets into a function - what an excellent use of a technology. So for mae at least it is whatever does the job.
Never underestimate the power of human stupidity RAH
-
( You are using SQL2005, right? :) ) Try this:
;WITH RecipientsByMail (MailId, RecipientList) AS (
select mx.Id AS MailId ,
( select AddressTable.Name+','
from MailTable
join RecipientTable on RecipientTable.MailTableId = MailTable.Id
join AddressTable on AddressTable.Id = RecipientTable.AddressTableId
WHERE MailTable.Id = mx.Id
FOR XML PATH('')
) AS RecipientList
from MailTable as mx
)
select m.Id, m.Created, m.Subject, sender.Name, r.RecipientList
from MailTable as m
join SenderTable as s on s.MailTableId = m.Id
join AddressTable as sender on sender.Id = s.AddressTableId
join RecipientsByMail as r on r.MailId = m.Id ;Please... SAVE my time by rating the posts that you read!
There are 10 kinds of people in the world: those who understand binary and those who don't.
Nope - the idea I had didn't work, the only other way I could get at this was to use a function which is even uglier!
Never underestimate the power of human stupidity RAH