rgb192
asked on
'staff3' can be the sender (which I want the query to show)
https://www.experts-exchange.com/questions/28284093/return-all-the-messages-with-profile-returned.html
I would like to see all the rows of messages with profile_id 'abl'
Because when I check results, I have to type select * from a_messages where profile_id='abl'
And this query shows all the messages where 'abl' is the sender
I want to see the messages where
'abl' is the profile_id
so
'abl' can be the sender (which the query currently shows)
AND
'staff3' can be the sender (which I want the query to show)
I would like to see all the rows of messages with profile_id 'abl'
Because when I check results, I have to type select * from a_messages where profile_id='abl'
And this query shows all the messages where 'abl' is the sender
I want to see the messages where
'abl' is the profile_id
so
'abl' can be the sender (which the query currently shows)
AND
'staff3' can be the sender (which I want the query to show)
SELECT
*
FROM (
SELECT
m.sender
, m.message_id
FROM (
SELECT
profile_id
, min(message_id) AS message_id
FROM a_messages2
GROUP BY
profile_id
) AS mn
INNER JOIN a_messages2 AS m ON mn.message_id = m.message_id
WHERE m.profile_id = m.sender
) AS ck
INNER JOIN a_messages2 AS m2 ON ck.sender = m2.sender
ORDER BY
m2.profile_id
, m2.message_id ASC
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')
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
No points for this, but beware of the word "this" as in "this_user" since "this" is a reserved keyword in many languages, including PHP. If you use it wrongly, it will cause you a lot of unwanted debugging time!
ASKER
Thank you for your help.
>>Eventually you will need sufficient knowledge of SQL
I am learning by modifying your queries.
For example I can now see messages initiated by staff by modifying your query.
I have a related question:
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
>>Eventually you will need sufficient knowledge of SQL
I am learning by modifying your queries.
For example I can now see messages initiated by staff by modifying your query.
I have a related question:
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
('swa','staff3','414288497
should be:
('swa','staff3','414288497
no action required here, but if repeating this in other questions it would be useful to fix then