Problems in an SQL query

Asked

Viewed 71 times

1

Hello,

in the following query, an error is shown in the:

select 
    fto.id_taxon, 
    fto.cd_sexo as fto_cd_sexo, 
    fto.cd_idade as fto_idade, 
    x.ftv_cd_sexo, 
    x.ftv_cd_idade, 
    x.id_fv 
from 
    tb_foto_ocorrencia fto 
    left join (
        select 
            ftv.id_taxon as id_t, 
            ftv.cd_sexo as ftv_cd_sexo, 
            ftv.cd_idade as ftv_cd_idade, 
            ftv.id_foto_ocorrencia as id_fo, 
            ftv.id_foto_validacao as id_fv, 
            sum(
                case when ftv.id_taxon <> fto.id_taxon then 1 else 0 end
            ) 
        from 
            tb_foto_validacao ftv 
        group by 
            id_fo
    ) x on fto.id_foto_ocorrencia = x.id_fo 
WHERE 
    fto.fl_validado = 'n' 
ORDER BY 
    `fto`.`id_foto_ocorrencia` ASC

The error shown is as follows::

1054 - Unknown column 'Fto.id_taxon' in 'field list'

But as can be seen in the query, the fields are defined.

mysql sql select join alias

  • The table has this column id_taxon?

  • Yes, as seen in the external select.

  • The error occurs in the case line when comparing: case when ftv.id_taxon <> Fto.id_taxon then 1 Else 0 end

  • A name for the column of sum() in subquery. The problem must be caused by this.

2 answers

0

When trying to rename the table to a temporary name one should use AS, that is, maybe AS is missing between the real table name and the temporary one, right after From.

select 
fto.id_taxon, 
fto.cd_sexo as fto_cd_sexo, 
fto.cd_idade as fto_idade, 
x.ftv_cd_sexo, 
x.ftv_cd_idade, 
x.id_fv from 
tb_foto_ocorrencia as fto 
left join (
    select 
        ftv.id_taxon as id_t, 
        ftv.cd_sexo as ftv_cd_sexo, 
        ftv.cd_idade as ftv_cd_idade, 
        ftv.id_foto_ocorrencia as id_fo, 
        ftv.id_foto_validacao as id_fv, 
        sum(
            case when ftv.id_taxon <> fto.id_taxon then 1 else 0 end
        ) 
    from 
        tb_foto_validacao as ftv 
    group by 
        id_fo
) x on fto.id_foto_ocorrencia = x.id_fo WHERE 
fto.fl_validado = 'n' ORDER BY 
`fto`.`id_foto_ocorrencia` ASC`

0

Elliott,

The error occurs because you are trying to access data from an external query within a sub-select. To fix the error, you must remove the fto.id_taxon of Sum() in the Sub Select.

I see that the column of sum is not being selected, and its sum is unnecessary. Anyway, it follows corrected query, with the sum being selected in the main select.

select 
    fto.id_taxon, 
    fto.cd_sexo as fto_cd_sexo, 
    fto.cd_idade as fto_idade, 
    x.ftv_cd_sexo, 
    x.ftv_cd_idade, 
    x.id_fv,
    sum(case when x.id_t <> fto.id_taxon then 1 else 0 end) Soma
from 
    tb_foto_ocorrencia fto 
    left join (
        select 
            ftv.id_taxon as id_t, 
            ftv.cd_sexo as ftv_cd_sexo, 
            ftv.cd_idade as ftv_cd_idade, 
            ftv.id_foto_ocorrencia as id_fo, 
            ftv.id_foto_validacao as id_fv 
        from 
            tb_foto_validacao ftv 
        group by 
            id_fo
    ) x on fto.id_foto_ocorrencia = x.id_fo 
WHERE 
    fto.fl_validado = 'n' 
ORDER BY 
    `fto`.`id_foto_ocorrencia` ASC

Browser other questions tagged mysql sql select join alias

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