• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 321
  • Last Modified:

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

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
rgb192
Asked:
rgb192
1 Solution
 
PortletPaulfreelancerCommented:
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
 
rgb192Author Commented:
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now