Champ inconnu error dans on clause - SQL

Asked

Viewed 457 times

0

I have the following situation, with 3 tables:

ocorrencias: Has 6 columns: tipo_falha_1 until tipo_falha_6. It’s a two-digit code, from 1 to 20.

tipo_falha: Has a description of the faults. The field cod is linked to occurrences by tipo_falha above. In addition, it has the column tipo_servico with a code that links to the type of service. Example: Electrical fault(tipo_falha), Electric (tipo_servico).

tipo_servico: It has the description of the service whose_typefails. The Cod field is linked to the_servicetype of the_typetable.

My need would be to generate a query, with 6 columns, of the types of failure from 1 to 6, bringing result where given and null where there is nothing. I tried SQL below, but it is returning the error: 

#1054 - Champ 'o.tipo_falha_1' inconnu dans on clause.

What could I be doing wrong?

SELECT tf1.NOME desc1, tf2.NOME AS desc2, tf3.NOME AS desc3, tf4.NOME AS desc4, tf5.NOME AS desc5, tf6.NOME AS desc6
               FROM ocorrencias o, tipo_servico ts LEFT JOIN
               tipo_falha tf1
               on tf1.cod = o.tipo_falha_1 LEFT JOIN
               tipo_falha tf2
               on tf2.cod = o.tipo_falha_2 LEFT JOIN
               tipo_falha tf3
               on tf3.cod = o.tipo_falha_3 LEFT JOIN
               tipo_falha tf4
               on tf4.cod = o.tipo_falha_4 LEFT JOIN
               tipo_falha tf5
               on tf5.cod = o.tipo_falha_5 LEFT JOIN    
               tipo_falha tf6
               on tf6.cod = o.tipo_falha_6
               where
               o.cod= 1
mysql sql join

  • You can see you’re telling me to give LEFT JOIN of tipo_servico calling for ocorrencias, and then there’s no Join ts right? If it worked, it would probably bring a lick in the final result. I believe that just reverse the order of the ts for o will not solve, it is better to take a look at the structure of the table, because this would only give error if the table did not have this field.

1 answer

1


You are doing a type LEFT JOIN with type_servico, but the condition of Join uses a field that is in occurrence.

  FROM ocorrencias o, tipo_servico ts LEFT JOIN
               tipo_falha tf1
               on tf1.cod = o.tipo_falha_1

Since you don’t use type_service for anything, why don’t you do so?

  FROM ocorrencias o LEFT JOIN
               tipo_falha tf1
               on tf1.cod = o.tipo_falha_1

Browser other questions tagged mysql sql join

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