Colleagues.
I need to search multiple tables (a total of 28) where each table corresponds to a state in Brazil. This search would be from the CEP, 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 inner in all the tables. Would you have any other solution?
At Wikipedia has information how the structure of a zip code 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 you just have to get the first digit of the zip code and you already have the information of which table in the DB it can be.
I created a new response because it took a completely different approach.
I checked the database you are using and with two queries you can return the ZIP code's address.
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
Save the UF field to a variable and do the next search
-- 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 do not know what language you're programming in, but it's very simple.
The first search is done with the first 5 digits (% with%).
While the second is done with all the digits and with the hyphen ( 99999 ).