need help with a query

  • 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.

  • 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
  • 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

  • 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

  • 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
  • And the article Luis recommended will help you understand the code I posted.

  • 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.

  • 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
  • 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?

  • 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.

  • 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.

  • 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.


    [font="Arial"]Low-hanging fruit picker and defender of the moggies[/font]

    For better assistance in answering your questions, please read this[/url].


    Understanding and using APPLY, (I)[/url] and (II)[/url] Paul White[/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