Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase 12»»

need help with a query Expand / Collapse
Author
Message
Posted Friday, February 7, 2014 3:35 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 9, 2014 1:53 PM
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.
Post #1539422
Posted Friday, February 7, 2014 4:24 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 4,041, Visits: 9,189
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1539434
Posted Friday, February 7, 2014 4:26 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 9, 2014 1:53 PM
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
Post #1539436
Posted Friday, February 7, 2014 4:47 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 20,857, Visits: 32,871
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






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)
Post #1539440
Posted Friday, February 7, 2014 4:48 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 4,041, Visits: 9,189
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1539441
Posted Friday, February 7, 2014 4:51 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 20,857, Visits: 32,871
And the article Luis recommended will help you understand the code I posted.


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)
Post #1539442
Posted Saturday, February 8, 2014 6:18 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 9, 2014 1:53 PM
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.
Post #1539542
Posted Saturday, February 8, 2014 8:01 PM


SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Yesterday @ 6:00 PM
Points: 4,041, Visits: 9,189
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.
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?

Forum Etiquette: How to post data/code on a forum to get the best help
Post #1539546
Posted Saturday, February 8, 2014 10:24 PM


SSC-Insane

SSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-InsaneSSC-Insane

Group: General Forum Members
Last Login: Today @ 12:34 AM
Points: 20,857, Visits: 32,871
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?



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)
Post #1539550
Posted Sunday, February 9, 2014 8:02 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Sunday, February 9, 2014 1:53 PM
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.
Post #1539566
« Prev Topic | Next Topic »

Add to briefcase 12»»

Permissions Expand / Collapse