Select Chained

Asked

Viewed 2,462 times

1

Hello, I have a question about a select chained. I found many examples with

select * from (select campo from tabela)

But I want to define the fields of my query, as below:

select campo1,campo2,(select campo1 from tabela2) from tabela1

The point is that inside the 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 search for another column?

sql

  • if you do not use Where will return all elements of your table2 which is not allowed within select, you actually have all the values of a table in a field of a select ?

  • I will comment on the actual case without examples 1 minute

  • I guess the mistake is just because you put two Where WHERE TPMOV <> 3 WHERE AG_MTARC.SEQ_TAREFA = AG_MMOV.SEQ_TAREFA exchanges the second for AND

  • 1

    That’s right, vlws! how do I close the post?

2 answers

1


By the name of the fields I believe Tabela1 and table 2 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

1

For this to be possible you need to keep in mind that subselet need to return only one value. You cannot return two values in the same alias and 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 the case of its "sub-concession" select campo1 from tabela2 where id=campo1 if you have returned two values will occur the error described above, for this reason will be an alias for Estocou and another to Vendeu.

Browser other questions tagged sql

You are not signed in. Login or sign up in order to post.