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

Question

Ok  here is my dilemma I have a database set up with about 5 tables all with the exact same data structure  The data is separated in this manner for localization purposes and to split up a total of about 4 5 million records   A majority of the time only one table is needed and all is well  However  sometimes data is needed from 2 or more of the tables and it needs to be sorted by a user defined column  This is where I am having problems   data columns   id  band name  song name  album name  genre   MySQL statment   SELECT   from us music  de music where  genre     punk    MySQL spits out this error    1052 - Column  genre  in where clause is ambiguous   Obviously  I am doing this wrong  Anyone care to shed some light on this for me

User · Answer

It sounds like you d be happer with a single table   The five having the same schema  and sometimes needing to be presented as if they came from one table point to putting it all in one table   Add a new column which can be used to distinguish among the five languages  I m assuming it s language that is different among the tables since you said it was for localization    Don t worry about having 4 5 million records   Any real database can handle that size no problem   Add the correct indexes  and you ll have no trouble dealing with them as a single table

User · Answer

The union statement cause a deal time in huge data  It is good to perform the select in 2 steps    select the id then select the main table with it

User · Answer

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

User · Answer

It sounds like you d be happer with a single table   The five having the same schema  and sometimes needing to be presented as if they came from one table point to putting it all in one table   Add a new column which can be used to distinguish among the five languages  I m assuming it s language that is different among the tables since you said it was for localization    Don t worry about having 4 5 million records   Any real database can handle that size no problem   Add the correct indexes  and you ll have no trouble dealing with them as a single table

User · Answer

Your original attempt to span both tables creates an implicit JOIN  This is frowned upon by most experienced SQL programmers because it separates the tables to be combined with the condition of how   The UNION is a good solution for the tables as they are  but there should be no reason they can t be put into the one table with decent indexing  I ve seen adding the correct index to a large table increase query speed by three orders of magnitude

User · Answer

The column is ambiguous because it appears in both tables you would need to specify the where  or sort  field fully such as us music genre or de music genre but you d usually specify two tables if you were then going to join them together in some fashion  The structure your dealing with is occasionally referred to as a partitioned table although it s usually done to separate the dataset into distinct files as well rather than to just split the dataset arbitrarily  If you re in charge of the database structure and there s no good reason to partition the data then I d build one big table with an extra  origin  field that contains a country code but you re probably doing it for legitimate performance reason  Either use a union to join the tables you re interested in http   dev mysql com doc refman 5 0 en union html or by using the Merge database engine http   dev mysql com doc refman 5 1 en merge-storage-engine html

User · Answer

The column is ambiguous because it appears in both tables you would need to specify the where  or sort  field fully such as us music genre or de music genre but you d usually specify two tables if you were then going to join them together in some fashion  The structure your dealing with is occasionally referred to as a partitioned table although it s usually done to separate the dataset into distinct files as well rather than to just split the dataset arbitrarily  If you re in charge of the database structure and there s no good reason to partition the data then I d build one big table with an extra  origin  field that contains a country code but you re probably doing it for legitimate performance reason  Either use a union to join the tables you re interested in http   dev mysql com doc refman 5 0 en union html or by using the Merge database engine http   dev mysql com doc refman 5 1 en merge-storage-engine html

User · Answer

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

User · Answer

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

User · Answer

The column is ambiguous because it appears in both tables you would need to specify the where  or sort  field fully such as us music genre or de music genre but you d usually specify two tables if you were then going to join them together in some fashion  The structure your dealing with is occasionally referred to as a partitioned table although it s usually done to separate the dataset into distinct files as well rather than to just split the dataset arbitrarily  If you re in charge of the database structure and there s no good reason to partition the data then I d build one big table with an extra  origin  field that contains a country code but you re probably doing it for legitimate performance reason  Either use a union to join the tables you re interested in http   dev mysql com doc refman 5 0 en union html or by using the Merge database engine http   dev mysql com doc refman 5 1 en merge-storage-engine html

User · Answer

Your original attempt to span both tables creates an implicit JOIN  This is frowned upon by most experienced SQL programmers because it separates the tables to be combined with the condition of how   The UNION is a good solution for the tables as they are  but there should be no reason they can t be put into the one table with decent indexing  I ve seen adding the correct index to a large table increase query speed by three orders of magnitude

User · Answer

Any of the above answers are valid  or an alternative way is to expand the table name to include the database name as well - eg   SELECT   from us music  de music where  us music genre     punk  AND  de music genre     punk

User · Answer

It sounds like you d be happer with a single table   The five having the same schema  and sometimes needing to be presented as if they came from one table point to putting it all in one table   Add a new column which can be used to distinguish among the five languages  I m assuming it s language that is different among the tables since you said it was for localization    Don t worry about having 4 5 million records   Any real database can handle that size no problem   Add the correct indexes  and you ll have no trouble dealing with them as a single table

User · Answer

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

User · Answer

Any of the above answers are valid  or an alternative way is to expand the table name to include the database name as well - eg   SELECT   from us music  de music where  us music genre     punk  AND  de music genre     punk

User · Answer

Any of the above answers are valid  or an alternative way is to expand the table name to include the database name as well - eg   SELECT   from us music  de music where  us music genre     punk  AND  de music genre     punk

User · Answer

The union statement cause a deal time in huge data  It is good to perform the select in 2 steps    select the id then select the main table with it

User · Answer

Your original attempt to span both tables creates an implicit JOIN  This is frowned upon by most experienced SQL programmers because it separates the tables to be combined with the condition of how   The UNION is a good solution for the tables as they are  but there should be no reason they can t be put into the one table with decent indexing  I ve seen adding the correct index to a large table increase query speed by three orders of magnitude

User · Answer

It sounds like you d be happer with a single table   The five having the same schema  and sometimes needing to be presented as if they came from one table point to putting it all in one table   Add a new column which can be used to distinguish among the five languages  I m assuming it s language that is different among the tables since you said it was for localization    Don t worry about having 4 5 million records   Any real database can handle that size no problem   Add the correct indexes  and you ll have no trouble dealing with them as a single table

User · Answer

Any of the above answers are valid  or an alternative way is to expand the table name to include the database name as well - eg   SELECT   from us music  de music where  us music genre     punk  AND  de music genre     punk

User · Answer

The column is ambiguous because it appears in both tables you would need to specify the where  or sort  field fully such as us music genre or de music genre but you d usually specify two tables if you were then going to join them together in some fashion  The structure your dealing with is occasionally referred to as a partitioned table although it s usually done to separate the dataset into distinct files as well rather than to just split the dataset arbitrarily  If you re in charge of the database structure and there s no good reason to partition the data then I d build one big table with an extra  origin  field that contains a country code but you re probably doing it for legitimate performance reason  Either use a union to join the tables you re interested in http   dev mysql com doc refman 5 0 en union html or by using the Merge database engine http   dev mysql com doc refman 5 1 en merge-storage-engine html

User · Answer

Your original attempt to span both tables creates an implicit JOIN  This is frowned upon by most experienced SQL programmers because it separates the tables to be combined with the condition of how   The UNION is a good solution for the tables as they are  but there should be no reason they can t be put into the one table with decent indexing  I ve seen adding the correct index to a large table increase query speed by three orders of magnitude

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

Examples related to sql

Examples related to mysql

Examples related to join

Examples related to mysql-error-1052