0
Colleagues.
I need to search several tables ( a total of 28 ) where each table corresponds to a state of Brazil. This search would be the zip code, which is one of the fields of these tables. Inner Join I do not know if it would be the solution, otherwise I would have to give input in all tables. Would there be some other solution?
1
In the Wikipedia has information on how the structure of a CEP works:
X____-___: Região
_X___-___: Sub-região
__X__-___: Setor
___X_-___: Subsetor
____X-___: Divisor de subsetor
_____-XXX: Sufixo de distribuição
With this data it is evident that just take the first digit of the zip code and you already have the information of which table in the BD it can be.
I’m basing myself on this site, but I want the search to take place inside my server, so it doesn’t depend on the external server. Veja: http://www.republicavirtual.com.br/busca_de_cep/PHP/#php In Examples, you can download the SQL of the Zip Code/Cities
1
I created a new answer because I had a completely different approach to the previous one.
I checked the database you are using and with two queries you can return to the ZIP code.
Step 1
-- Exemplo: CEP 97060-003
SELECT
uf.UF,
uf.Nome
FROM uf
WHERE "97060" BETWEEN uf.Cep1 AND uf.Cep2;
+----+-------------------+
| uf | Nome |
+----+-------------------+
| RS | Rio Grande do Sul |
+----+-------------------+
Step 2
Store the UF field in a variable and do the next query
-- Exemplo: CEP 97060-003
SELECT *
FROM rs as uf
WHERE uf.cep = "97060-003"
+-------+-------------+--------------------------+--------+-----------+---------------+
| id | cidade | logradouro | bairro | cep | tp_logradouro |
+-------+-------------+--------------------------+--------+-----------+---------------+
| 20410 | Santa Maria | Nossa Senhora Medianeira | Centro | 97060-003 | Avenida |
+-------+-------------+--------------------------+--------+-----------+---------------+
I don’t know what language you’re programming in, but it’s pretty simple.
The first search is done with the first 5 digits (99999).
While the second is done with all digits and with the hyphen(99999-999).
Browser other questions tagged mysql sql
You are not signed in. Login or sign up in order to post.
Which field will be used for search criteria?
– fernandosavio
Hello Fernando. The field will be the cep.
– user24136
I’m not sure now, but if I’m not mistaken each state has a zip code range. If you know this range, you can already know directly on which table to look.
– fernandosavio
I’m sorry Fernando, I couldn’t understand. What exactly would range? The fields that compose all the tables are: id, city, street, neighborhood, zip code and tp_street. They are the same fields for all the tables
– user24136
You can use views to optimize the search, here are some advantages: View the data without storing the data in the object. Limit the display of a table ex. can hide some of the columns in the tables. Join two or more tables and show as a single object to the user. Restrict access to a table so that no one can insert new rows. http://stackoverflow.com/questions/10302615/mysql-views-performance
– Marcus Becker
Why these data are not in a single table?
– Reginaldo Rigo
Because I am creating a system where the user will type a zip code and will automatically appear in the street, state and city. Based on republicavirtual.com.br/, I downloaded their SQL, but I don’t want to be held hostage by external servers to bring this result. Their SQL already comes this way. A database called CEP and several tables with the name of the states (sp, Rj, mg,...).
– user24136
I found very abstract this question, better elaborate the structure of the tables, which information you want and which filter you need to use.
– Edson Horacio Junior
I get it. Anyway you could join all these tables that you downloaded in only one to facilitate your search, or you will have to search in the correct table depending on the cep informed. Or put it all together through a UNION ALL and search on the resulting table, but performance will get bad.
– Reginaldo Rigo