CODEWITHSUNDEEP
mysqlsql
user2324375
user2324375

Reputation: 5

How to join and get data with multiple table but the same structure?

Join multiple table and get data in one line.

tbl_2012-08 (structure) 

id | data_log | data_name 

1  | 0001     | first
2  | 0002     | second


tbl_2012-09 (structure) 

id | data_log | data_name 

1  | 0003     | third


Output:

data_log
0001
0002
0003

How could I join this 2 table so that I can extract data at once.
any case would help
like:
create another table or something

Upvotes: 0

Views: 248

Answers (3)

Taryn
Taryn

Reputation: 247860

I don't know why you have separate tables for each month but you should be able to use a UNION query to return the data from both tables:

select id, data_log, data_name
from `tbl_2012-08`
union all
select id, data_log, data_name
from `tbl_2012-09`

I used a UNION ALL to return all rows from both tables which will include duplicates (if any). You cannot JOIN the tables unless you have some common value in both tables and if you have separate tables for each month then I would guess that you don't have a common value in both tables.

As side note, I might include include a column so you can easily identify what table the data is coming from:

select id, data_log, data_name, '2012-08' mth
from `tbl_2012-08`
union all
select id, data_log, data_name, '2012-09' mth
from `tbl_2012-09`

My suggestion would be to look at changing this data structure, having a separate table for each month will get very cumbersome to manage.

If you want to just return the data_log, then you just use:

select data_log
from `tbl_2012-08`
union all
select data_log
from `tbl_2012-09`

Upvotes: 5

Gordon Linoff
Gordon Linoff

Reputation: 1270733

You should use union all. But, you should also include a column identifying where the data came from. In this case, I assume it is the date:

SELECT '2012-08' as YYYYMM, id, data_log, data_name
FROM `tbl_2012-08`
UNION ALL
SELECT '2012-09' as YYYYMM, id, data_log, data_name
FROM`tbl_2012-09`;

In addition, if you want the id to be unique after bringing them together, then this will work:

select YYYYMM, (@rn := @rn + 1) as id, id as old_id, data_log, data_name
from ((SELECT '2012-08' as YYYYMM, id, data_log, data_name
       FROM `tbl_2012-08`
      ) UNION ALL
      (SELECT '2012-09' as YYYYMM, id, data_log, data_name
       FROM`tbl_2012-09`
      )
     ) t cross join
     (select @rn := 0) const;

You can also create another table by doing:

create table new_table as
    <either of the queries above>

Upvotes: 0

Kevin Kunderman
Kevin Kunderman

Reputation: 2134

You should uses UNION ALL

SELECT id, data_log, data_name
FROM `tbl_2012-08`
UNION ALL
SELECT id, data_log, data_name
FROM`tbl_2012-09`

In this case the Union will join the result from one select statement with the other to make one table. Its important to note that both select statements must return the same number of columns.

Upvotes: 0

Related Questions