Compare data from two tables

Question

I have two tables:

Tabela1                        Tabela2
+------+----------+--------+   +------+----------+------------+ 
|  id  | idCidade | bairro |   |  id  | idBairro | logradouro |
+------+----------+--------+   +------+----------+------------+
| 6091 |   15890  | Vila A |   |  05  |   6091   | Av. Brasil |
| 5089 |   17500  | Vila A |   |  08  |   1000   | Av. Brasil |
+------+----------+--------+   +------+----------+------------+

The user will inform the 'street', 'idCidade'. I need to know which neighborhood is a given street in a given city. With the name of the 'street' I search in Tabela2, in the example above it returns to me the existence of two places with this name in destintos neighborhoods. But I also do a search in Tabela1, I search the 'id' of all neighborhoods of a given city. If for example I inform that (idCidade=17500) and (logradouro=Av Brasil), the neighborhood of this street would be 'Vila A' of id=5089. What query would you use for this?

Answer 1

In the case of a table merge you should use a JOIN:

SELECT tb1.id,
       tb1.bairro
  FROM Tabela1 tb1
       INNER JOIN Tabela2 tb2 ON tb2.idBairro = tb1.id
 WHERE tb1.idCidade = 17500
   AND tb2.logradouro = 'Av Brasil';

In this case the INNER JOIN will merge the tables according to the idBairro. From the merge result, we filter according to the city code on Tabela1 and the backyard of Tabela2;

If you would like to learn more about the operation of JOIN, you can check it in this excellent question answer What is the difference between INNER JOIN and OUTER JOIN?

---

Note: In your example, for the expected result to be returned, it would be necessary to have the following line in Tabela2:

 +------+----------+------------+ 
 |  id  | idBairro | logradouro |
 +------+----------+------------+
 |  12  |   5089   | Av. Brasil |
 +------+----------+------------+

Answer 2

SELECT * FROM bairro left join logradouro  
on (bairro.id = logradouro.idBairro)
and (logradouro.logradouro like '%Av Brasil%')

Answer 3

Already set up address system so, the relationship is very simple, just connect via table id, the DBMS itself will create new lines for each different address.

SELECT
    cidade,
    bairro,
    logradouro
FROM
    logradouro L
    LEFT JOIN bairro B ON B.idBairro = L.idBairro
    LEFT JOIN cidade C ON C.idCidade = B.idCidade
WHERE
    B.idCidade = 17500 AND
    logradouro = 'Av Brasil'

I have directly used the name of the tables I have requested you to use.