-1

My tables are below:

crm_conversions

crm_conversions crm_messages_of_conversions crm_messages_of_conversions
crm_receivers_of_messages crm_receivers_of_messages I want to get all of conversions of an user with only last message. I'm trying to get with the query:

SELECT `conv`.`subject`, `mc`.`message_text`, `mc`.`sentDateTime`, `mc`.id AS "msgId", `conv`.id AS "convId"
FROM (`crm_receivers_of_messages` rm)
JOIN `crm_messages_of_conversions` mc ON `mc`.`id` = `rm`.`m_id`
JOIN `crm_conversions` conv ON `conv`.`id` = `mc`.`conv_id`
WHERE `rm`.`receiver_id` =  '1' 
GROUP BY convId
HAVING MAX(msgId)

But that query does not to give me true result: result I wanna to get msgId = 1 of convId 1, msgId = 4 of convId = 2

1 Answer 1

0

Solved:

SELECT `conv`.`subject`, `mc`.`message_text`, `mc`.`sentDateTime`, `mc`.id AS "msgId", `conv`.id AS "convId"
FROM (`crm_receivers_of_messages` rm)
JOIN `crm_messages_of_conversions` mc ON `mc`.`id` = `rm`.`m_id`
JOIN `crm_conversions` conv ON `conv`.`id` = `mc`.`conv_id`
WHERE `rm`.`receiver_id` =  '1' AND mc.id = (SELECT MAX(id) FROM crm_messages_of_conversions WHERE conv.id = crm_messages_of_conversions.conv_id)
GROUP BY convId
2
  • 1
    And what happens if you remove the GROUP BY? Mar 25, 2015 at 14:07
  • return same result. you are very careful thanks :)
    – Mesuti
    Mar 25, 2015 at 14:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.