SitePoint Sponsor

User Tag List

Results 1 to 3 of 3
  1. #1
    SitePoint Member
    Join Date
    Jan 2006
    Location
    Bristol, UK
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Subquery (?) to get latest message for a support logging system

    Hi,

    I'm trying to put together a list of all the support calls we have logged in our MS SQL 2000 server based database. I'm having trouble getting a single row for each call that contains the last response that isnt a comment.

    Here's some simplified info on 2 of the tables invloved, and a bit of made up data:

    Table 1: Support_Calls

    ID (AUTOINT), Description (NTEXT), Date_Logged (DATETIME), Platform (CHAR) (etc, etc)
    1, 'This doesnt work', 20/01/2006, 'WinXP',......
    2, 'That doesnt work', 25/01/2006, 'MacOS',......

    Table 2: Support_Call_Messages

    ID (AUTOINT), SUPPORT_CALL_ID (INT), MESSAGE_DATE (DATETIME), MESSAGE_TYPE (VARCHAR), MESSAGE (NTEXT)

    1, 1, 21/01/2006, 'E-mail','Some text here'
    2, 1, 22/01/2006, 'Comment','Need to look at this further'
    3, 2, 26/01/2006, 'E-Mail', 'Please try this instead'


    What I'm looking to achieve is a result set with one row per support call, with all the columns from 'SUPPORT_CALLS' and including the last message, message_date etc from 'SUPPORT_CALL_MESSAGES' for the call, but not 'COMMENT's.

    So for my sample data I'd like to return something like:

    ID, Description, Date_Logged, Platform, MESSAGE_DATE, MESSAGE_TYPE, MESSAGE
    1, 'This doesnt work', 20/01/2006, 'WinXP', 21/01/2006, 'E-mail','Some text here'
    2, 'That doesnt work', 25/01/2006, 'MacOS',26/01/2006, 'E-Mail', 'Please try this instead'

    Even though record 2 of SUPPORT_CALL_MESSAGES is the latest response for SUPPORT_CALL 1, I'd like it ignored as it is a COMMENT type, and to retrieve the "E-mail" type instead.

    I've looked through the forums a bit and found some things that look similar and tried the suggestions but am getting multiple rows per call. I've been heading down this sort of road:

    SELECT *
    FROM support_call_message SCM
    LEFT OUTER JOIN
    (SELECT support_call_id, MAX(message_date) AS last_email_date
    FROM support_call_message
    WHERE (message_type <> 'COMMENT')
    GROUP BY support_call_id) INNERTABLE
    ON SCM.support_call_id = INNERTABLE.support_call_id

    I got to this by doing the inner query seperatly to get the last message that's not a comment per support call, which seemed to work, but when I join it, to get the other columns in support_call_message, I get several rows returned for some calls. Sadly I didn't really get any further than this, let alone bringing the main support_calls table into things!

    Any guidance would be much appreciated!
    Cheers,
    Chris

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,270
    Mentioned
    60 Post(s)
    Tagged
    3 Thread(s)
    Code:
    select SC.*
         , SCM.message_date
         , SCM.message_type
         , SCM.message 
      from Support_Calls as SC 
    left outer 
      join Support_Call_Messages as SCM
        on SCM.support_call_id
         = SC.ID
       and SCM.message_type <> 'COMMENT'
       and SCM.message_date
         = ( select max(message_date)  
               from Support_Call_Messages
              where support_call_id = SC.ID
                and message_type <> 'COMMENT' )
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Member
    Join Date
    Jan 2006
    Location
    Bristol, UK
    Posts
    4
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Red face

    That's brilliant, many thanks!!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •