tags:

views:

54

answers:

2

I have 6 tables with exactly the same structure, the field names go like this:

country | incoming | outgoing_national | outgoing_us | outgoing_uk | outgoing_global | outgoing_text

I'm trying to code a rates table where the rates change instantly (jusing jQuery) when a country is selected from a dropdown. Because this is going to be happening as soon as they choose a country it needs to be as fast as possible.

So basically for example where country = 'England' I need the data from all the different tables so I can display it all on the page. I've been playing about with a code I've got from a previous similar question but I'm getting confused trying to work it out. I'd appreciate a helping hand of any sort :D

+1  A: 
SELECT * FROM Table_1 WHERE country='England'
UNION 
SELECT * FROM Table_2 WHERE country='England'
.
.
.

and so on.

If you want to see rows that are identical in two or more tables use UNION ALL

idstam
+1  A: 

The fastest way to do this is via union all. Of course, that doesn't remove duplicates, so if you need to do that, then use union instead (though you'll take a performance hit). Moreover, you'll want to index the country column in all of the tables. Doing this will pull back all of those queries very quickly.

Secondarily, why do you have six tables of all the same structure? This seems a bit out of place to me.

To get all of the countries available:

select distinct
    country
from table1

To get all of the data within a country:

select
    *
from
    table1 t1
where
    country = 'England'
union all
select
    *
from
    table2 t2
where
    country = 'England'

...

union all
select
    *
from
    table6 t6
where
    country = 'England'
Eric
very good point about the six tables with the same structure... seriously
jle
Because they are all for different phone tarrifs.. I could put them all in one table but it wouldn't be as convenient and also I need to have a dropdown list of the countries so to get this list all I have to do is display country names from table A.
zuk1
@zuk: Did you need the query to also get the countries?
Eric
@zuk: you should then put a field indicating the type of tarif in the first table. and drop all the others.
Leonel Martins
Yeh but then I'm going to have duplicate countries in the one table.
zuk1
If you think there is a significance speed boost by having it in one table I'll do that.
zuk1
@sik1: if you have a index on `country` you can get the countries easy and fast by doing the first query Eric posted. And not havind to do `JOIN`s will get a significant speed boost. At least, give it a try: make an database of test and try it. If you´ll have a few records in each table, maybe it isnt worth changing your model (program). mysql will handle yours just fine.
Leonel Martins