DOUBT About the use of SELECT and Standard Tables

Question

I have a question as to the advantages of having standardised tables and their forms of use within a SELECT. 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 to be able to 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 don’t know another way to display this data with only 1 query in the database.

I know that the use of normalized tables comes from good practices and as security to hinder "SQL Injection" in the system. However the code is not only extensive but still has to be observed so that there are no errors regarding the parameters of the ID informed so that there is no mismatch of the information.

My question is: There is a more practical way to get the same data, without so many (SELECT) embedded in the code?

Answer 1

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. Then you could release an SQL this way:

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 table requests to the source and destination tables and their proper addresses. Note the use of INNER JOIN, the query says that you want to redeem all records contained in the request table and obligatory (INNER JOIN) have records that relate to the attribute id_source and id_destination in their respective tables as well as the tables tb_source and tb_destination must relate to the table tb_addressee by attribute id_address.

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

In order to have a request, a origin and destination as well as their respective addresses.

Let’s say there is a new table that relates may or may not contain related records, in this case we would launch the use of LEFT JOIN.

I built this example based on your wish above, but did not do any modeling on the bench nor test. Adapt to your liking! The concept to relate using normalized structures is this, I do not advise to follow another path however easy it seems, there ahead will generate serious problems about expansion and maintenance.

Hugs.