February 7, 2014 at 3:35 pm
Not quite sure how to write this query and I thought someone would be so kind as to assist me.
I have two tables (The column names have been changed for security):
message table
============
messageid
carbon_copy_id
senderid
recipientid
carbon_copy table
==============
carbon_copy_id
member_id
Essentially I am writing an email system that allows carbon copies.
- When a member sends a message to one recipient, it writes one record to the message table, leaving the carbon_copy_id null.
- When a member sends an email that has more than one recipient, say for eg 2 recipients,
I insert two records into the carbon copy table with the same carbon_copy_id and a member_id for each recipient. I then insert two message records, one for each recipient into the message table with the shared carbon_copy_id i inserted into the carbon copy table.
Prior to doing this, my selects were simple:
select messageid,carbon_copy_id
from message_table
where messageid = id_of_message
order by messageid desc
However, given the paradigm outlined above, this will produce a list of the two records inserted. What I am looking to do is have the result set combine the two records into one and return a comma delimited list of the carbon_copied member ids where carbon_copy_id in the message record is not null.
The idea is to return a list of messages and not display duplicate messages when the message is carbon copied. Similar to how GMAIL does it.
Any help would be much appreciated.
February 7, 2014 at 4:24 pm
As long as this is just for display purposes (you shouldn't store comma-delimited values), here is an option you could use: http://www.sqlservercentral.com/articles/comma+separated+list/71700/
February 7, 2014 at 4:26 pm
Its not stored as comma delimited values. I'm saying I want the query to produce a comma delimited list based on the join while simultaneously combining records that share a common carbon_copy_id
February 7, 2014 at 4:47 pm
Since you did not provide anything from which to really work (DDL or sample data), this is the best I can do for you. Not sure if it will work as I could not test it.
select
mt.messageid,
mt.carbon_copy_id,
oa.member_list
from
message_table mt
outer apply (select stuff((select ', ' + cast(cc.member_id as varchar)
from carbon_copy cc
where cc.carbon_copy_id = mt.carbon_copy_id
order by cc.member_id
for xml path(''),TYPE).value('.','varchar(max)'),1,2,''))oa(member_list)
where
messageid = id_of_message
order by
messageid desc
February 7, 2014 at 4:48 pm
And I'm telling you the article will show you how to do it. 😉
Read it, understand it, try to get a solution and if you have any specific questions, feel free to ask.
February 7, 2014 at 4:51 pm
And the article Luis recommended will help you understand the code I posted.
February 8, 2014 at 6:18 pm
Thanks guys. We're a little bit closer.
So I tried your query. It worked in that it produced the comma delimited list, which is great.
The 2nd part of it is this:
Your query returns both records, each with the comma delimited list.
What I need is where the carbon_copy_id is not null, it only returns one of the two. Where it is null, it only returns one record.
so for eg, the resultset of your query produced
messageid carboncopyid memberlist
175241638678, 25256
175241538678, 25256
i would need this to be one record with the memberlist where carbon copy was not null, one record without it where it is null.
February 8, 2014 at 8:01 pm
You could use a distinct or give us some sample data to test the solutions provided. You can check how to do it on the article linked in my signature.
February 8, 2014 at 10:24 pm
youresoshain (2/8/2014)
Thanks guys. We're a little bit closer.So I tried your query. It worked in that it produced the comma delimited list, which is great.
The 2nd part of it is this:
Your query returns both records, each with the comma delimited list.
What I need is where the carbon_copy_id is not null, it only returns one of the two. Where it is null, it only returns one record.
so for eg, the resultset of your query produced
messageid carboncopyid memberlist
175241638678, 25256
175241538678, 25256
i would need this to be one record with the memberlist where carbon copy was not null, one record without it where it is null.
For the below:
so for eg, the resultset of your query produced
messageid carboncopyid memberlist
175241638678, 25256
175241538678, 25256
Why would one row have a list of member id's and the other be null when the message id's are different? Is the duplication a result of the same carbon copy id's?
February 9, 2014 at 8:02 am
I think what I need to do here is two queries and union the two.
There are two scenarios, a message from one sender to one recipient.
A message from one sender to multiple recipients.
These are essentially two messages since the latter, while being sent to multiple recipients, is the same message, duplicated to multiple recipients.
When producing a list, I don't want to show multiple copies of the same message. Imagine, if you used gmail, and instead of showing grouped messages, it displayed a copy of each one. Its not an ideal presentation of that information.
February 9, 2014 at 1:09 pm
So i ended up going with a union query that syncs grouped by carbon_copy_id and one without and it seems to be doing the job.
Thanks guys for getting me this far.
February 9, 2014 at 1:43 pm
youresoshain (2/9/2014)
So i ended up going with a union query that syncs grouped by carbon_copy_id and one without and it seems to be doing the job.Thanks guys for getting me this far.
You have some of the finest TSQL developers in the world trying to figure out your problem for you in the sustained absence of sample data and expected output from that data.
They've done their best (and they've made some astonishingly good guesses given the scant information they've been provided with).
Have you?
Think on this, the next time you're handed a spec with vital information missing. It's a two-way street.
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply