MySQL - Selecting Data from Multiple Tables All with Same Structure But Different Data

MySQL - Selecting data from multiple tables all with same structure but different data

I think you're looking for the UNION clause, a la

(SELECT * from us_music where `genre` = 'punk')
UNION
(SELECT * from de_music where `genre` = 'punk')

How can I select data from two tables with same structure but not related? MySQL

Check out MySQL UNION Syntax:

(SELECT * FROM table_A) UNION (SELECT * FROM table_B)

python/mysql: SELECTing from multiple tables overwriting duplicate column in resulting dictionary

SELECT * ... may lead to ambiguities when there are conflicting column names.

You should set aliases for conflicting column names.

Also set short aliases for the table names that can shorten the code and make it more readable and use them to qualify all the column names.

The implicit join syntax that you use has been replaced, since many years, by explicit join syntax.

Your code should be written like this:

sql = f"""
SELECT c.customer_id, c.customer_name, c.address customer_address,
       p.partner_id, p.partner_name, p.address partner_address
FROM customer c
INNER JOIN customer_partner cp ON c.customer_id = cp.customer_id 
INNER JOIN partner p ON cp.partner_id = p.partner_id
WHERE c.customer_id IN ({','.join(['%s' for _ in range(len(customer_id_list))])})
"""

I left out all the columns of customer_partner from the SELECT list because they are not needed.

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

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`

Query Multiple table with same structure

You can create a view of all possible tables:

create v_tables as
    select 'table1' as which, t.*
    from table1 t
    union all
    select 'table2' as which, t.*
    from table2 t
    . . .;

Then, you can select from the view.

In MySQL this is not particularly efficient, because all the tables will still need to be read. However, for smaller tables this could be a reasonable solution.

You should ask yourself why you have identically structured tables in the databases. In general, these should be combined into a single table. For performance, you can partition the table.

---

Related Topics