Hello, I'm having a question about a select chained. I found many examples with
select * from (select campo from tabela)
However I want to define the fields of my query, as below:
select campo1,campo2,(select campo1 from tabela2) from tabela1
The point is that within select I need a where with a column displayed
select campo1,campo2,(select campo1 from tabela2 where id=campo1) from tabela1
How can I index to fetch through another column?
By the name of the fields I believe table1 and table2 are the same table. So you need to define an alias for them so that sql can differentiate the field from one to another.
SELECT T1.campo1,
T1.campo2,
(select T2.campo1 from tabela2 T2 where T1.id=T2.campo1) as campo3
FROM tabela1 T1
Edited
Considering the information in the answer below,
SELECT T1.id,
T1.id venda,
(select T2.nome from tabela2 T2 where T1.id = T2.id) as nome
FROM tabela1 T1
For this to be possible you need to keep in mind that the subselet information needs to return only one value. You can not return two values in the same alias and you may receive the error: MySQL error 1241: Operand should contain 1 column(s) .
SELECT
cod,
nome,
desc,
(SELECT
SUM(dado)
FROM
tabela_auxiliar
WHERE
cod = 1) as Vendeu,
(SELECT
SUM(dado)
FROM
tabela_auxiliar
WHERE
cod = 2) as Estocou
FROM
tabela_principal;
In case of your "subquery" select campo1 from tabela2 where id=campo1 if you return two values will occur the error described above, for this reason it will be an alias for Estocou and another one for Vendeu .