Get subquery ID field that just pulls MySQL name

Question

Get subquery ID field that just pulls MySQL name

Navigation

#1 by (1 votes)

0

Good afternoon!

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

See the last two subqueries to retrieve 2 fields as 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 I run this query, I'm getting the error:

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

I believe it should be because treId does not exist in the subquery , since I'm only having 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 table of flights by operator ( sistema_voo_operadora ), saving the global flight ID ( vooId ) 3) In the table of global flights, I have the ID of the destination and source snippets. These IDs are from a table named sistema_trecho ( treId ).

As I need a query to bring both in the same place, so I do not have to do N queries, I would like to bring all the infos in one place. I know it's possible, but I do not know if this is the right way.

Attempts: If I put in addition to trtTitulo , a trtId in the subqueries , they no longer work as well.

mysql

asked by anonymous 23.10.2018 / 19:28

1 answer

Extending javascript function with parameters How to pass to the AJAX select option for codeigniter view?

score 1 · Answer 1

The error displayed 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 name of the "table" is different from the selected alias ( tre instead of treo ) - the good news is that the query is ok.

As for the query itself, it is extremely costly to process; in addition to the four joins it has eight subqueries (which are executed for each field of each row returned).

As these subqueries are made up of just two tables, it would be more interesting to use them in joins remembering to use join or subquery 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 query will not be very performative; perhaps it is better to revise the structuring of the bank or a more efficient query (to think about ...)