Solved

I want to see all the rows of data which I need to respond to  (the messages I sent and received)

Posted on 2013-11-08
2
303 Views
Last Modified: 2013-11-15
This question is related to

http://www.experts-exchange.com/Database/MySQL/Q_28280747.html

CREATE TABLE `a_messages2` (
       `a_messages_id` INT (11) auto_increment  primary key
     , `profile_id` VARCHAR(20)
     , `sender` VARCHAR(20)
     , `message_id` BIGINT (20)
     , `this_user` VARCHAR(20)
     );



INSERT INTO a_messages2
          (`profile_id`, `sender`, `message_id`, `this_user`)
    VALUES
('abl', 'staff3', 3662615602, 'staff3'),
('abl', 'abl', 3660687080, 'staff3'),
('abl', 'staff3', 3658803944, 'staff3'),
('abl', 'staff3', 3649952930, 'staff3'),
('abl', 'staff3', 3644250249, 'staff3'),
('abl', 'staff3', 3614235515, 'staff3'),
('abl', 'abl', 3614140442, 'staff3'),
('abl', 'staff3', 3613984751, 'staff3'),
('abl', 'staff3', 3613936332, 'staff3'),
('abl', 'abl', 3613469574, 'staff3'),
('abl', 'staff3', 3612970481, 'staff3'),
('abl', 'abl', 3609276901, 'staff3'),
('abl', 'abl', 3609276896, 'staff3'),
('abl', 'staff3', 3866086756, 'staff3'),
('abl', 'staff3', 3866079064, 'staff3'),
('abl', 'abl', 3856964132, 'staff3'),
('abl', 'staff3', 3844779912, 'staff3'),
('abl', 'staff3', 3916948647, 'staff3'),
('abl', 'staff3', 3880367425, 'staff3'),
('abl', 'abl', 3977088222, 'staff3'),
('abl', 'staff3', 3974513295, 'staff3'),
('abl', 'staff3', 3973285282, 'staff3'),
('abl', 'abl', 3960907806, 'staff3'),
('abl', 'staff3', 4003664468, 'staff3'),
('abl', 'abl', 4002246444, 'staff3'),
('abl', 'staff3', 3996089944, 'staff3'),
('abl', 'staff3', 4024726432, 'staff3'),
('abl', 'abl', 4021516823, 'staff3'),
('abl', 'staff3', 4018957318, 'staff3'),
('abl', 'abl', 4050609080, 'staff3'),
('abl', 'staff3', 4052368867, 'staff3'),
('abl', 'abl', 4052424820, 'staff3'),
('abl', 'staff3', 4053183569, 'staff3'),
  
('swa','swa','4153199171','staff3'),
('swa','staff3','4150323049','staff3'),
('swa','swa','4146362776','staff3'),
('swa','staff3','4142884979','staff3'),
('swa','staff3','4142884976','staff3')
('DTw', 'DTw', '3658976250', 'staff3'),
('DTw', 'DTw', '3658422687', 'staff3'),
('DTw', 'DTw', '3653399588', 'staff3'),
('DTw', 'DTw', '3653174764', 'staff3'),
('DTw', 'DTw', '3546098525', 'staff3'),
('DTw', 'DTw', '3545571594', 'staff3'),
('DTw', 'DTw', '3545571593', 'staff3'),
('DTw', 'DTw', '3540574071', 'staff3'),
('DTw', 'DTw', '3540454344', 'staff3'),
('DTw', 'DTw', '3540376883', 'staff3'),
('fre', 'fre', '3544889093', 'staff3'),
('gam', 'gam', '3955910171', 'staff3'),
('gam', 'gam', '3955910165', 'staff3'),
('gam', 'gam', '3649458031', 'staff3'),
('gam', 'gam', '3648816297', 'staff3'),
('gam', 'gam', '3648783076', 'staff3'),
('gam', 'gam', '3648516109', 'staff3'),
('gam', 'gam', '3647758123', 'staff3'),
('gam', 'gam', '3554774961', 'staff3'),
('gam', 'gam', '3540425395', 'staff3'),
('gam', 'gam', '3540357159', 'staff3'),
('gam', 'gam', '3540345288', 'staff3'),
('h1a', 'staff3', '3700557963', 'staff3'),
('h1a', 'h1a', '3693946789', 'staff3'),
('h1a', 'h1a', '3693806133', 'staff3'),
('h1a', 'staff3', '3684800156', 'staff3'),
('h1a', 'h1a', '3684173904', 'staff3'),
('h1a', 'h1a', '3682095987', 'staff3'),
('h1a', 'h1a', '3682095983', 'staff3'),
('h1a', 'h1a', '3542388954', 'staff3'),
('nyh', 'nyh', '3554817690', 'staff3'),
('nyh', 'nyh', '3554796334', 'staff3'),
('nyh', 'nyh', '3554701760', 'staff3'),
('nyh', 'nyh', '3554689575', 'staff3'),
('nyh', 'nyh', '3541014726', 'staff3'),
('nyh', 'nyh', '3541012663', 'staff3'),
('nyh', 'nyh', '3540619436', 'staff3'),
('nyh', 'nyh', '3540474531', 'staff3')

Open in new window





need to find out if a customer is waiting for a staff member to respond


Real world explanation:
Respond to the customers waiting for a response.

there should be no results because
select profile_id, sender,message_id,this_user from a_messages2 where profile_id='abl' order by message_id desc limit 1

abl  staff3  4053183569  staff3


staff3 was the last to respond

we do not need to send another message to 'abl' because staff3 already sent the last message


but there should be results for swa, because we need to respond to swa





This query works but
I want to see all the rows of data for 'swa' (the messages I sent and received)

SELECT
        mx.sender
      , mx.message_id
FROM (
      SELECT
        profile_id
      , sender
      , max(message_id) AS message_id
      FROM a_messages2
      WHERE profile_id=sender
      GROUP BY
        profile_id
      , sender
     ) AS mx
LEFT JOIN a_messages2 AS m ON mx.profile_id =  m.profile_id
                          AND mx.sender     <> m.sender
                          AND mx.message_id <  m.message_id
WHERE m.a_messages_id IS NULL
;

/* http://sqlfiddle.com/#!9/2093c/7 */

Open in new window

0
Comment
Question by:rgb192
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
2 Comments
 
LVL 48

Accepted Solution

by:
PortletPaul earned 500 total points
ID: 39634932
I'm quite happy to continue helping but some of these things you should be able to do. Eventually you will need sufficient knowledge of  SQL to maintain this series of queries and if you don't try you won't gain those skills.

The existing query locates the wanted sender(s). So, use this now as a subquery and join it  to the messages table by sender, this will then list all the related messages. Here's the result of that approach against the sample data of this question:
| A_MESSAGES_ID | PROFILE_ID | SENDER | MESSAGE_ID | THIS_USER |
|---------------|------------|--------|------------|-----------|
|            34 |        swa |    swa | 4153199171 |    staff3 |
|            36 |        swa |    swa | 4146362776 |    staff3 |
|            39 |        DTw |    DTw | 3658976250 |    staff3 |
|            40 |        DTw |    DTw | 3658422687 |    staff3 |
|            41 |        DTw |    DTw | 3653399588 |    staff3 |
|            42 |        DTw |    DTw | 3653174764 |    staff3 |
|            43 |        DTw |    DTw | 3546098525 |    staff3 |
|            44 |        DTw |    DTw | 3545571594 |    staff3 |
|            45 |        DTw |    DTw | 3545571593 |    staff3 |
|            46 |        DTw |    DTw | 3540574071 |    staff3 |
|            47 |        DTw |    DTw | 3540454344 |    staff3 |
|            48 |        DTw |    DTw | 3540376883 |    staff3 |
|            49 |        fre |    fre | 3544889093 |    staff3 |
|            50 |        gam |    gam | 3955910171 |    staff3 |
|            51 |        gam |    gam | 3955910165 |    staff3 |
|            52 |        gam |    gam | 3649458031 |    staff3 |
|            53 |        gam |    gam | 3648816297 |    staff3 |
|            54 |        gam |    gam | 3648783076 |    staff3 |
|            55 |        gam |    gam | 3648516109 |    staff3 |
|            56 |        gam |    gam | 3647758123 |    staff3 |
|            57 |        gam |    gam | 3554774961 |    staff3 |
|            58 |        gam |    gam | 3540425395 |    staff3 |
|            59 |        gam |    gam | 3540357159 |    staff3 |
|            60 |        gam |    gam | 3540345288 |    staff3 |
|            69 |        nyh |    nyh | 3554817690 |    staff3 |
|            70 |        nyh |    nyh | 3554796334 |    staff3 |
|            71 |        nyh |    nyh | 3554701760 |    staff3 |
|            72 |        nyh |    nyh | 3554689575 |    staff3 |
|            73 |        nyh |    nyh | 3541014726 |    staff3 |
|            74 |        nyh |    nyh | 3541012663 |    staff3 |
|            75 |        nyh |    nyh | 3540619436 |    staff3 |
|            76 |        nyh |    nyh | 3540474531 |    staff3 |

Open in new window

Here is the query. Notice lines 7 to 23 - that was the previous query, now used as a subquery:
SELECT
         m2.profile_id
       , m2.sender
       , m2.message_id
       , m2.this_user
FROM (
      SELECT
              mx.sender
      FROM (
            SELECT
              profile_id
            , sender
            , max(message_id) AS message_id
            FROM a_messages2
            WHERE profile_id=sender
            GROUP BY
              profile_id
            , sender
           ) AS mx
      LEFT JOIN a_messages2 AS m ON mx.profile_id =  m.profile_id
                                AND mx.sender     <> m.sender
                                AND mx.message_id <  m.message_id
      WHERE m.a_messages_id IS NULL
	 ) AS mmx
INNER JOIN a_messages2 AS m2 ON mmx.sender = m2.sender
;

Open in new window


http://sqlfiddle.com/#!9/f3ef1/9
0
 

Author Closing Comment

by:rgb192
ID: 39651025
Thank you for your help.

>>Eventually you will need sufficient knowledge of  SQL
I am learning by modifying your queries

messages for profiles that I wrote first AND are waiting for a response.
http://www.experts-exchange.com/Database/MySQL/Q_28295129.html
0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

This guide whil teach how to setup live replication (database mirroring) on 2 servers for backup or other purposes. In our example situation we have this network schema (see atachment). We need to replicate EVERY executed SQL query on server 1 to…
Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
In a recent question (https://www.experts-exchange.com/questions/29004105/Run-AutoHotkey-script-directly-from-Notepad.html) here at Experts Exchange, a member asked how to run an AutoHotkey script (.AHK) directly from Notepad++ (aka NPP). This video…

735 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question