Selecting data from multiple tables at once

[ajmoore]

Okay, I've got an issue here where I'm trying to select data from multiple tables in one database in one query. I realize that the way things are set up in this situation is not ideal but this is an external database that I had no control over setting up and have no access to modify the structure. Basically, there are 5 identical tables set up...the only difference between them is the name of each table, the fields are all the same. I need to select all the data from all of the tables but preferably without running 5 different queries. Once all the data is selected I have to have some way to know which piece of data came from which table...Is it possible to do this with one query or do I just have to run seperate queries for each of the tables?

Thanks,
ajmoore

[Lars_Berg]

A union query combines the result of one or more select statements

select c1,c2 ,'T1' from t1
where 
  c3 = 4711
union all
select c1,c2 ,'T2' from t2
where 
  c3 = 4711
union all
select c1,c2 ,'T3' from t3
where 
  c3 = 4711
union all
select c1,c2 ,'T4' from t4
where 
  c3 = 4711
union all
select c1,c2 ,'T5' from t5
where 
  c3 = 4711

[jblack]

I've never had to do this, but you can do it with a JOIN statement in MySQL. Since I've never done it, I'm attaching the URL with the syntax to JOIN.

A page on it is:

http://www.mysql.com/doc/en/JOIN.html

This is a way you can get all the data back from the tables at once. You could change the column names in the tables, so they are a bit different, so you can tell what data came from what table.