rgb192
asked on
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
https://www.experts-exchange.com/questions/28280747/indicate-row-or-profile-id-which-needs-to-be-responded-to.html
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_use r 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)
https://www.experts-exchange.com/questions/28280747/indicate-row-or-profile-id-which-needs-to-be-responded-to.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')
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_use
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 */
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
>>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.
https://www.experts-exchange.com/questions/28295129/messages-for-profiles-that-I-wrote-first-AND-are-waiting-for-a-response.html