Home / Questions / 276931
Accepted
Fabian
Asked: 2020-07-06 01:12:42 +0800 CST 2020-07-06 01:12:42 +0800 CST

How can I show the info of a fk with a select?

  • 772

I explain, I have a table called employee

CREATE TABLE IF NOT EXISTS empleado(
    numEmpleado int PRIMARY KEY NOT NULL,
    nombre VARCHAR(100) NOT NULL,
    email VARCHAR(100) NOT NULL,
    sueldo bigint NOT NULL,
    fContratacion DATE NOT NULL,
    numSucursal int,
    jefe int NULL,
    CONSTRAINT fkjefe
    FOREIGN KEY (jefe) REFERENCES empleado(numEmpleado),
    CONSTRAINT fksucursal
    FOREIGN KEY (numSucursal) REFERENCES sucursal(numSucursal)
);

I have to do a select showing the name, the salary, the date of hiring and the name of the boss.

My problem is that when I show the boss his id appears (numEmpleado)

How can I show the name of the boss?

I have this select

SELECT nombre, sueldo, fContratacion, jefe FROM empleado;

but instead of the id of the boss I should display his name.

I saw that I can create a view, or a select with join, but it doesn't work for me :(

mysql

1 Answers

  1. Best Answer

    You have to do a JOINwith the same table relating boss with the boss's EmployeeNumber.

    SELECT e.nombre, e.sueldo, e.fContratacion, e.jefe, e2.nombre as nombre_jefe FROM empleado e
JOIN empleado e2 ON e2.numEmpleado  = e.jefe;

    The name comes in the field nombre_jefe.

    • 1