CODEWITHSUNDEEP
mysqlsqlselectmultiple-tables
user961627
user961627

Reputation: 12747

Select similar columns from different tables in MySQL

I have 2 tables that have some columns which are very similar.

The first table, paypal_orders, has the following columns, and let's say it has 15 records:

The second table cod_orders has the following columns, and let's say it has 10 records:

I was thinking of using SELECT INTO... but discovered that MySQL's support for it only extends to user defined variables and outfiles.

Is it possible to make a SELECT statement that can give an output of 25 records, displaying only the columns that are common to both tables (with an additional "type" column):

So the first 15 records will be the paypal orders, with type "paypal", and the next 10 will be the cod orders, with type = "cod".

Upvotes: 1

Views: 127

Answers (1)

Kevin Bowersox
Kevin Bowersox

Reputation: 94459

A union should allow you to get both result sets.

(select 'paypal', amount, status, date, transaction_id 
                                                   from paypal_orders limit 15)
union
(select 'cod', amount, status, date, id 
                                     from cod_orders limit 10)

SQL Fiddle: http://sqlfiddle.com/#!2/037e5/3

Upvotes: 1

Related Questions