Compare data from two tables

Question

Compare data from two tables

Navigation

#1 by (1 votes)
#2 by (0 votes)
#3 by (0 votes)

0

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 'public place', 'idCidade'. I need to know what is the neighborhood of a certain place in a certain city. With the name of the 'backyard' I look in Table 2, in the example above it returns me to the existence of two places with that name in districts. But I also do a search in Table 1, I look for the 'id' of all neighborhoods of a certain city. If for example I inform you that (idCidade = 17500) and (backradouro = Av Brasil), the neighborhood of this street would be 'Vila A' of id = 5089. What would be the query you would use for this?

php mysql sql-select

asked by anonymous 31.05.2017 / 19:38

3 answers

0

I have already set up the address system so the relationship is very simple, just connect via id of the tables, that 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 directly used the name of the tables that I assume you use.

31.05.2017 / 20:02

0

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

31.05.2017 / 19:49

Change document after post-save middleware in mongoose TypeError: uri.indexOf is not a function

score 1 · Accepted Answer

In case of a join of tables 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 perform the join of the tables according to idBairro . From the result of the junction, we filter according to the city code in Tabela1 and the street of Tabela2 ;

If you'd like to know more about how JOIN works, you can check it out in this great answer to What is the difference between INNER JOIN and OUTER JOIN?

Note: In your example, for the expected result to be returned, you would need the following line in Tabela2 :

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