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

sql mysql join mysql-error-1052
Jayrox picture Jayrox · Jan 3, 2009 · Viewed 182k times · Source

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?

Answer

Mihai Limbășan picture Mihai Limbășan · Jan 3, 2009

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')

Related questions

1052: Column 'id' in field list is ambiguous

I have 2 tables. tbl_names and tbl_section which has both the id field in them. How do I go about selecting the id field, because I always get this error: 1052: Column 'id' in field list is ambiguous Here's my …

Read More
What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN?

What's the difference between INNER JOIN, LEFT JOIN, RIGHT JOIN and FULL JOIN in MySQL?

Read More
How to do a FULL OUTER JOIN in MySQL?

I want to do a Full Outer Join in MySQL. Is this possible? Is a Full Outer Join supported by MySQL?

Read More