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

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Introduction In this article, I will by showing a nice little trick for MySQL similar to that of my previous EE Article for SQLite (http://www.sqlite.org/), A SQLite Tidbit: Quick Numbers Table Generation (http://www.experts-exchange.com/A_3570.htm…
Foreword This is an old article.  Instead of using the MySQL extension that was used in the original code examples, please choose one of the currently supported database extensions instead.  More information is available here: MySQLi / PDO (http://…

737 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