Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


need help with a query


need help with a query

Author
Message
youresoshain
youresoshain
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
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.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18129
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/


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
youresoshain
youresoshain
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
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
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24186 Visits: 37954
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





Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18129
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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24186 Visits: 37954
And the article Luis recommended will help you understand the code I posted.

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
youresoshain
youresoshain
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
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

1752416 3 8678, 25256
1752415 3 8678, 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.
Luis Cazares
Luis Cazares
SSCrazy Eights
SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)SSCrazy Eights (8.5K reputation)

Group: General Forum Members
Points: 8530 Visits: 18129
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.


Luis C.
General Disclaimer:
Are you seriously taking the advice and code from someone from the internet without testing it? Do you at least understand it? Or can it easily kill your server?


How to post data/code on a forum to get the best help: Option 1 / Option 2
Lynn Pettis
Lynn Pettis
SSC-Insane
SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)SSC-Insane (24K reputation)

Group: General Forum Members
Points: 24186 Visits: 37954
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

1752416 3 8678, 25256
1752415 3 8678, 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

1752416 3 8678, 25256
1752415 3 8678, 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?

Cool
Lynn Pettis

For better assistance in answering your questions, click here
For tips to get better help with Performance Problems, click here
For Running Totals and its variations, click here or when working with partitioned tables
For more about Tally Tables, click here
For more about Cross Tabs and Pivots, click here and here
Managing Transaction Logs

SQL Musings from the Desert Fountain Valley SQL (My Mirror Blog)
youresoshain
youresoshain
Forum Newbie
Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)Forum Newbie (5 reputation)

Group: General Forum Members
Points: 5 Visits: 8
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.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search