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

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

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

Suggested Solutions

All XML, All the Time; More Fun MySQL Tidbits – Dynamically Generate XML via Stored Procedure in MySQL Extensible Markup Language (XML) and database systems, a marriage we are seeing more and more of.  So the topics of parsing and manipulating XM…
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…
Microsoft Active Directory, the widely used IT infrastructure, is known for its high risk of credential theft. The best way to test your Active Directory’s vulnerabilities to pass-the-ticket, pass-the-hash, privilege escalation, and malware attacks …
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below. https://filedb.experts-exchange.com/incoming/2017/03_w12/1151775/Permutations.txt https://filedb.experts-exchange.com/incoming/201…

840 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