join two mysql queries based on parameters

Question

hi there

i have two queries, querying the same table, but based on different parameters,

i then need to mush these two result sets together based on certian parameters

//get initial text
Q1
SELECT 
campaign_id AS campaign_id, 
from_number AS mobile,
received_msg AS join_txt,
date_received AS join_txt_date
 FROM received_txts WHERE action_id = 4 AND msg_link_id = id;

//get final text
Q2
SELECT 
campaign_id AS campaign_id, 
from_number AS mobile,
received_msg AS final_txt,
date_received AS final_txt_date
 FROM received_txts WHERE action_id = 4 AND msg_complete_id = id;

/join these two queries on
Q2.msg_link_id = Q1.id AND Q2.campaign_id = Q1.campaign_id AND Q2.from_number = Q1.from_number

Answer 1

use a virtual table:

SELECT *
FROM table AS t1
JOIN ( select .. ) AS t2
ON ( t1.foo = t2.foo )

Answer 2

SELECT 
  Q1.campaign_id AS campaign_id, 
  Q1.from_number AS mobile,
  Q1.received_msg AS join_txt,
  Q1.date_received AS join_txt_date,
  Q2.received_msg AS final_txt,
  Q2.date_received AS final_txt_date
FROM received_txts Q1 JOIN received_txts Q2
  ON Q2.msg_link_id = Q1.id
    AND Q2.campaign_id = Q1.campaign_id
    AND Q2.from_number = Q1.from_number
WHERE Q1.action_id = 4
  AND Q2.action_id = 4
  AND Q1.msg_link_id = Q2.id
  AND Q2.msg_complete_id = Q2.id