DOUBT Overuse of SELECT and Standard Tables

Question

DOUBT Overuse of SELECT and Standard Tables

Navigation

#1 by (2 votes)

4

I have a question about the advantages of having standard tables and how to use them within a SELECT. Let's suppose the following tables:

 -------------- 
| tb_endereco  |
 --------------
| id           |
| rua          |
| numero       |
| bairro       |
| latitude     |
| longitude    |
|______________|

 -------------- 
|tb_solicitacao|
 --------------
| id           |
| id_origem    |
| id_destino   |
|______________|

 -------------- 
| tb_origem    |
 --------------
| id           |
| id_endereco  |
| responsavel  |
| observacoes  |
|______________|

 -------------- 
| tb_destino   |
 --------------
| id           |
| id_endereco  |
| responsavel  |
| observacoes  |
|______________|

Suppose, also, that the customer needs a table with the following information regarding Delivery number 1:

 ----------------------------------------------------------------------------------
| rua_origem | lat_origem | long_origem | rua_destino | lat_destino | long_destino |
 ----------------------------------------------------------------------------------

In case you can return the data as requested SELECT should be:

SELECT
(SELECT ende.rua FROM tb_endereco AS ende
 INNER JOIN tb_origem AS orig ON orig.id_endereco = ende.id
 INNER JOIN tb_solicitacao AS entr ON entr.id_origem = orig.id
 WHERE entr.id = 1) AS 'rua_origem',

(SELECT ende.latitude FROM tb_endereco AS ende
 INNER JOIN tb_origem AS orig ON orig.id_endereco = ende.id
 INNER JOIN tb_solicitacao AS entr ON entr.id_origem = orig.id
 WHERE entr.id = 1) AS 'lat_origem',

(SELECT ende.longitude FROM tb_endereco AS ende
 INNER JOIN tb_origem AS orig ON orig.id_endereco = ende.id
 INNER JOIN tb_solicitacao AS entr ON entr.id_origem = orig.id
 WHERE entr.id = 1) AS 'long_origem',

(SELECT ende.rua FROM tb_endereco AS ende
 INNER JOIN tb_destino AS dest ON dest.id_endereco = ende.id
 INNER JOIN tb_solicitacao AS entr ON entr.id_origem = dest.id
 WHERE entr.id = 1) AS 'rua_destino',

(SELECT ende.latitude FROM tb_endereco AS ende
 INNER JOIN tb_destino AS dest ON dest.id_endereco = ende.id
 INNER JOIN tb_solicitacao AS entr ON entr.id_origem = dest.id
 WHERE entr.id = 1) AS 'lat_destino',

(SELECT ende.longitude FROM tb_endereco AS ende
 INNER JOIN tb_destino AS dest ON dest.id_endereco = ende.id
 INNER JOIN tb_solicitacao AS entr ON entr.id_origem = dest.id
 WHERE entr.id = 1) AS 'long_destino'
FROM tb_solicitacao WHERE tb_solicitacao.id = 1

I do not know of another way to display this data with only 1 query in the database.

I know that the use of standard tables comes from good practices and as a form of security to hinder "SQL injection" in the system. However the code besides being extensive still has to be observed so that there are no errors regarding the ID parameters informed so that there is no mismatch of the information.

My question is: Is there a more practical way of getting the same data, without so many (SELECT) embedded in the code?

mysql select tabela

asked by anonymous 13.12.2017 / 07:09

1 answer

Error making application deploy in GlassFish: org.objectweb.asm.ClassReader.init - java.lang.ArrayIndexOutOfBoundsException Real-time account for table generated with jQuery

score 2 · Accepted Answer

The relationship you are trying to establish is not clear. If I understand correctly I believe that this is a 1: 1 relationship between the tables. So I could throw an SQL like this:

SELECT 
  end_origem.rua,
  end_origem.latitude,
  end_origem.longitude,
  end_destino.rua,
  end_destino.latitude,
  end_destino.longitude 
FROM 
  tb_solicitacao s INNER JOIN 
                               tb_origem  o ON  s.id_origem  = o.id 
                   INNER JOIN 
                               tb_destino d ON  s.id_destino =  d.id 
                   INNER JOIN 
                               tb_endereco end_origem  ON o.id_endereco = end_origem.id 
                   INNER JOIN 
                               tb_endereco end_destino ON d.id_endereco = end_destino.id 
WHERE 
      s.id = 1;

This simple example relates the requests table to the source and destination tables and their due addresses. Notice the use of INNER JOIN , the query says that you want to retrieve all the records that contain the request table and must have records that are related by attribute < strong> target_id and target_id in their respective tables, as well as the tb_origem and tb_destino tables must necessarily relate to the tb_endereco table with the id_address .

INNER JOIN will only redeem if it contains records in all relationship tables;

In order to exist a request, there must be a source and a destination as well as their respective addresses.

Let's say there is a new table that relates whether or not it contains related records, in which case you would launch the use of LEFT JOIN .

I set up this example based on your querie above, but did not do any modeling on the bench and neither test. Tailor to your liking! The concept to relate using standard structures is this, I do not advise you to go another way, no matter how easy it seems, up front it will cause serious problems about expansion and maintenance.

Hugs.