Select Chained

1

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?

    
asked by anonymous 03.08.2017 / 16:42

2 answers

1

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
    
03.08.2017 / 16:50
1

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 .     

03.08.2017 / 16:57