Mysql从两个具有相同结构的不同表中选择

Question

can you offer to me something, how to push two different tables but with the same structure, i means all info is the same, just table name is different, mailings and mailings_archive. for one table it is ok

$query = sprintf("SELECT * FROM %smailings  WHERE hidden=0 AND draft=1 %sORDER BY category_id, mailing_segment", $this->dbprefix, $language);

$this->mailings[] = array('id' => $row->id,
                                      'mailing_naam' => $row->mailing_naam,
                                      'mailing_subject' => $row->mailing_subject,
                                      'mailing_from_name' => $row->mailing_from_name,
                                      'mailing_bounceaddress' => $row->mailing_bounceaddress,
                                      'mailing_body' => $row->mailing_body,
                                      'mailing_status' => $row->mailing_status,
                                      'mailing_segment' => $row->mailing_segment,
                                      'mailing_total_subscribers' => $row->mailing_total_subscribers,
                                      'mailing_total_sent' => $row->mailing_total_sent,
                                      'category_id' => $row->category_id,
                                      'utm_campaign' => $row->utm_campaign,
                                      'language' => $row->language);
        }                       

Now it looks like this, how to put in this array, another table data ? Dont forget, every field is the same, just table name different.

Answer 1

(SELECT * FROM %smailings  WHERE hidden=0 AND draft=1 %sORDER BY category_id, mailing_segment)
UNION
(SELECT * FROM another_table WHERE hidden=0 AND draft=1 %sORDER BY category_id, mailing_segment)

(select * from table1)
union
(select * from table2)

But this will result in one unordered result set, even if you put "order by" inside of each request, but this one probably the fastest.

If you need to have everything ordered between tables (1....8), then this is a syntax:

select * from table1
union
select * from table2 order by id

If you want to have two separate orders (like 4...8 from one table, 1..4 from another):

select * from 
(select * from test1 order by id asc) as t1
union all
(select * from test2 order by id asc);