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
307 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 49

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

What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

Question has a verified solution.

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

Foreword In the years since this article was written, numerous hacking attacks have targeted password-protected web sites.  The storage of client passwords has become a subject of much discussion, some of it useful and some of it misguided.  Of cou…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This is a high-level webinar that covers the history of enterprise open source database use. It addresses both the advantages companies see in using open source database technologies, as well as the fears and reservations they might have. In this…
This video Micro Tutorial shows how to password-protect PDF files with free software. Many software products can do this, such as Adobe Acrobat (but not Adobe Reader), Nuance PaperPort, and Nuance Power PDF, but they are not free products. This vide…

734 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