Grab subquery ID field that only draws Mysql name

Question

Good afternoon!

I have an SQL query and would like to get 2 additional fields from other tables, but with other names so that they are not ambiguous.

See the last two Queries to rescue 2 fields like trtTituloOrigem and trtTituloDestino, respectively.

SELECT *, 

(SELECT treNome FROM sistema_trecho treo WHERE tre.treId = voo.vooOrigemId) as treNomeOrigem, 
(SELECT treEnderecoCidade FROM sistema_trecho treo WHERE treo.treId = voo.vooOrigemId) as treEnderecoCidadeOrigem, 
(SELECT treEnderecoEstado FROM sistema_trecho treo WHERE treo.treId = voo.vooOrigemId) as treEnderecoEstadoOrigem, 

(SELECT treNome FROM sistema_trecho tred WHERE tred.treId = voo.vooDestinoId) as treNomeDestino, 
(SELECT treEnderecoCidade FROM sistema_trecho tred WHERE tred.treId = voo.vooDestinoId) as treEnderecoCidadeDestino,
(SELECT treEnderecoEstado FROM sistema_trecho tred WHERE tred.treId = voo.vooDestinoId) as treEnderecoEstadoDestino,

(SELECT trtTitulo FROM sistema_trecho_tipo trt WHERE trt.trtId = treo.trtId) as trtTituloOrigem,
(SELECT trtTitulo FROM sistema_trecho_tipo trt WHERE trt.trtId = tred.trtId) as trtTituloDestino

FROM sistema_voo_operadora vop 

LEFT JOIN sistema_operadora ope ON vop.opeId = ope.opeId 
LEFT JOIN sistema_voo voo ON vop.vooId = voo.vooId 
LEFT JOIN sistema_tipo_voo tiv ON voo.tivId = tiv.tivId 
LEFT JOIN sistema_aeronave aer ON vop.aerId = aer.aerId 

WHERE ope.opeId = 2;

When running this query, I am getting the error:

Unknown 'tre.treId' column in 'Where clause'

I believe it must be because treId does not exist in the subquery, since I’m only bringing trtTitulo, right? But how can I bring these two fields?

Just to explain the whole context:

1) I have a global flight schedule (sistema_voo) 2) I have a flight schedule by operator (sistema_voo_operadora), storing the global flight ID (vooId) 3) In the global flight table, I have the ID of the destination and source stretches. These Ids are from a table called sistema_trecho (treId).

Since I need a consultation to bring both in the same place, so I don’t have to make N appointments, I would like to bring all the hints in one place. I know it’s possible, but I don’t know if this is the right way.

Attempts: If I put beyond the trtTitulo, one trtId in Subqueries, they stop working too.

Answer 1

The error shown in the query is typing only; on the line causing the error (SELECT treNome FROM sistema_trecho treo WHERE tre.treId = voo.vooOrigemId) as treNomeOrigem, the "table" name is different from the selected alias (tre instead of treo) - the good news is that the query is okay.

As for the query itself, it is extremely expensive to process; in addition to the four joins she’s eight sub-discharge (which are executed for each field of each returned row).

Like these Subqueries are made on top of only two tables, it would be more interesting to use them in joins (remembering to use Join or subconsulta requires an analysis in each case). I suggest you use it like this:

SELECT *, 
treo.treNome as treNomeOrigem, 
treo.treEnderecoCidade as treEnderecoCidadeOrigem, 
treo.treEnderecoEstado as treEnderecoEstadoOrigem, 

tred.treNome as treNomeDestino, 
tred.treEnderecoCidade as treEnderecoCidadeDestino,
tred.treEnderecoEstado as treEnderecoEstadoDestino,

trto.trtTitulo as trtTituloOrigem,
trtd.trtTitulo as trtTituloDestino

FROM sistema_voo_operadora vop 

LEFT JOIN sistema_operadora ope ON vop.opeId = ope.opeId 
LEFT JOIN sistema_voo voo ON vop.vooId = voo.vooId 
LEFT JOIN sistema_tipo_voo tiv ON voo.tivId = tiv.tivId 
LEFT JOIN sistema_aeronave aer ON vop.aerId = aer.aerId 

LEFT JOIN sistema_trecho treo ON treo.treId = voo.vooOrigemId
LEFT JOIN sistema_trecho tred ON tred.treId = voo.vooDestinoId

LEFT JOIN sistema_trecho_tipo trto ON trto.trtId = tredo.trtId
LEFT JOIN sistema_trecho_tipo trtd ON trtd.trtId = tred.trtId

WHERE ope.opeId = 2;

Still the query will not be very performative; perhaps it is better to review the structure of the bank or a more efficient consultation (if you think...)