Good morning, I hope you can help me. I need to make the following query: Obtain the full name of the employees, as well as the name of the department, position and salary in which I have worked.
I have the first query that is the following:
SELECT id_emp, id_pose, id_depto, max (start_date) as ultimo_puesto FROM labora GROUP BY id_emp;
Which shows me what are the employees that I need to later know their full name, department position and salary, but I do not know how to make that query.
Welcome to Stackoverflow.
To obtain the data you need, you only have to join the tables ( JOIN ) with the reference keys and group them when necessary.
Let's start joining tables:
SELECT
e.Nombre,
e.Paterno,
e.Materno,
d.Nombre Departamento,
p.Descripcion Puesto,
c.Salario
FROM
empleado e
INNER JOIN
(
SELECT Id_Emp, MAX(Fecha_Inicio), Id_Depto, Id_Puesto
FROM labora GROUP BY Id_Emp, Id_Depto, Id_Puesto
) l
ON e.Id_Emp = l.Id_Emp
INNER JOIN departamento d ON l.Id_Depto = d.Id_Depto
INNER JOIN puesto p ON l.Id_Puesto = p.Id_Puesto
INNER JOIN contiene c ON p.Id_Puesto = c.Id_Puesto AND d.Id_Depto = c.Id_Depto
GROUP BY e.Id_Emp
I have not tried the query, but it should work.
Although I see some perhaps complicated points in your data design:
labora . I am not convinced that this table has been conceived as a kind of history where the dates in which an employee occupied a position and a department are kept at the same time.