join two mysql queries based on parameters
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_txt开发者_开发问答s 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
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
use a virtual table:
SELECT *
FROM table AS t1
JOIN ( select .. ) AS t2
ON ( t1.foo = t2.foo )
精彩评论