MySQL returns "# 1054 - The column 'person.type' in on clause is unknown"

Question

MySQL returns "# 1054 - The column 'person.type' in on clause is unknown"

Navigation

#1 by (3 votes)
#2 by (2 votes)

3

I have this sentence:

SELECT registro_vehiculo.fecha_entrada, persona.nombre,tipo_servicio.tipo_servicio

FROM registro_vehiculo,persona,tipo_servicio 
INNER JOIN tipo_persona ON tipo_persona.id_tipop=persona.tipo 
WHERE registro_vehiculo.fecha_entrada >= '2017-01-01' 
AND registro_vehiculo.fecha_entrada <='2017-04-20' 
ORDER BY registro_vehiculo.fecha_entrada ASC

and this error marks me

MySQL has said:

# 1054 - The column 'person.type' in on clause is unknown

Does anyone know the reason for the error?

The structure of my "person" table is this:

create table persona (
      id_persona int not null, 
      tipo int not null, 
      nombre varchar (20) not null,
      apellidos varchar (30) not null,
      telefono char(15) not null,
      direccion varchar (25) not null,
      correo varchar (30) not null,
      rfc varchar (20)not null, 
      primary key(id_persona),
      index pers_tipo(tipo),
      foreign key (tipo) references tipo_persona(id_tipop));

and the table "type_person" is:

create table tipo_persona (
             id_tipop int, 
             primary key(id_tipop) , 
             tipo_persona varchar(10) not null);

As you can see, I do have a foreign key in my "person" table.

mysql sql join

asked by Diego Pineda Cervantes 08.06.2017 в 14:59

source

2 answers

2

This looks like a problem in the order of operators. I think the problem is in the from of the query:

FROM registro_vehiculo,tipo_servicio , persona
INNER JOIN tipo_persona ON tipo_persona.id_tipop=persona.tipo

The inner join relates one table to the next. In the original query you are trying to relate service_type to person type (hence the error). Change the order of the tables. .

answered by 08.06.2017 в 15:29

Differences between file reading classes How to leave a service running in the background on Android by closing the application that starts it

score 3 · Accepted Answer

You have to change the position of the Personas table in the From, so that the table on which you make the join is correct and the condition can be applied. Since in your case you are doing the join on the table tipo_servicio .

Your query would be as follows:

SELECT registro_vehiculo.fecha_entrada,
     persona.nombre,
     tipo_servicio.tipo_servicio    
FROM registro_vehiculo,
     tipo_servicio,
     persona 
     INNER JOIN tipo_persona 
     ON tipo_persona.id_tipop=persona.tipo 
WHERE registro_vehiculo.fecha_entrada >= '2017-01-01' 
AND registro_vehiculo.fecha_entrada <='2017-04-20' 
ORDER BY registro_vehiculo.fecha_entrada ASC