joining? 9 tables of same named fields 1

[estrellas]

hey guys - don't get too frustrated with this (i'm really new to mysql).

this could be a terrible mistake, but i have a data set of about 5000 records. since it's going to be accessed online i decided to split it into 9 tables. (split up by region ... they're just data sets of station names, elevation, latitude, and longitude).

now i'm trying to create a page where the user can specifically create a query.

in order to submit this query, he will be querying all 9 tables ...

how do i do a join on the tables with the same field names?

my tables are set up like this:

table1: name, latitude, longitude, elevation
table2: name, latitude, longitude, elevation
table3: name, latitude, longitude, elevation
table4: name, latitude, longitude, elevation
table5: name, latitude, longitude, elevation
table6: name, latitude, longitude, elevation
table7: name, latitude, longitude, elevation
table8: name, latitude, longitude, elevation
table9: name, latitude, longitude, elevation

thanks for any help that you guys may have in advance

PS.
Am I anywhere close here with this query?
$latitude, $longitude, $elevation and $name are passed in by the user through a form ... and the +/-1's at the end of $latitude and $longitude mean that the user can input something within a +/-1 of the actual lat/long ... same with elevation except +/-50 .. (not sure if the syntax is right on that though)

$query = "SELECT *.* FROM ak,ca,hi,imw,ma,ne,pnw,pr 
WHERE *.name like '%$name%' 
and *.latitude="'$latitude'+1" 
or *.latitude="'$latitude'-1" 
and *.longitude="'$longitude'+1" 
or *.longitude="'$longitude'-1" 
and *.elevation="'$elevation'+50" 
or *.elevation="'$elevation'-50"";

If programming were as fun as cycling, then we'd need more bike mechanics.

 

[sleipnir214]

To be honest, there's no good reason for splitting up the data. I strongly recommend that you put all the data into one table and if necessary add an additional column to the table which records the region.

Your code will be a lot simpler.


Want the best answers? Ask the best questions!

TANSTAAFL!!

 

[r937]

one table

definitely



rudy

http://r937.com/

SQL Consulting