I'm using three tables in the database and query that I should combine. The tables look like:
Table message:
message_id | msg_id | conv_id | text | user_id
--------------------------------------------------------------
1 | 1 | 1 | hi | 12
2 | 1 | 1 | Thanks | 14
3 | 2 | 4 | helo | 21
Table conversation:
conv_id | msg_id | conv_id | subject | user_id | text
------------------------------------------------------------------------
1 | 1 | 1 | sub1 | 12 | Hi to all
2 | 1 | 1 | sub1 | 14 | Hi to all
3 | 2 | 4 | sub3 | 21 | reply2
the both table having msg_id is the common ,
my expected result is,
from conversation table group by msg_id ,
from messsage table message.msg_id=conversation.msg_id
conv_id | msg_id | conv_id | subject | text
-----------------------------------------------------------------
1 | 1 | 1 | sub1 | Hi to all
1 | 1 | 1 | sub1 | hi
2 | 1 | 1 | sub1 | thanks
3 | 2 | 4 | sub3 | helo
my query is
SELECT *
FROM mailbox_message m1
LEFT JOIN mailbox_conversation m2 ON m1.conversation_id=m2.conversation_id
WHERE m2.msg_id='1'
ORDER BY m1.created DESC
my query returns the following
conv_id | msg_id | conv_id | subject | text
-----------------------------------------------------------------
1 | 1 | 1 | sub1 | Hi to all
2 | 1 | 1 | sub1 | Hi to all
1 | 1 | 1 | sub1 | hi
2 | 1 | 1 | sub1 | thanks
3 | 2 | 4 | sub3 | helo