Union MySQL - Multitasking Procedure

Question

Union MySQL - Multitasking Procedure

Navigation

#1 by (3 votes)

0

Problem statement:

Perform a procedure with the following two functionalities:

• Alphabetically arranged list of waiters indicating their name, surname, ID and the person in charge.

• Alphabetically arranged list of participating chefs indicating their name, surname, ID card and the specialty they have.

I have the procedure well, without failures. The fact is that when making the union to show all the results, the value cookers.speciality is shown in charge as you can see in the image. Is there any way to show another column for cocineros.especialidad? I hope you have understood me, I enclose a picture of what I want to do and the script.

    drop procedure if exists proc2;

DELIMITER //

create procedure proc2()

begin

select empleados.nombre, empleados.apellido, empleados.dni, camareros.encargado from empleados, camareros
where empleados.codemple=camareros.codemple group by empleados.nombre
union
select empleados.nombre, empleados.apellido, empleados.dni, cocineros.especialidad from empleados, cocineros
where empleados.codemple=cocineros.codemple group by empleados.nombre;

end //

DELIMITER ;

call proc2();

mysql mysql-workbench

asked by 12.02.2018 в 17:58

source

1 answer

How to get a single value of a Json [duplicate] create a json file in Python 3.6.1

score 3 · Accepted Answer

You can add an empty column in each of the two selections you make. As union it detects that you have the same number of columns, and of the same type, it lets you do it without problems.

This is how you get one column for each type of output you need.

Example:

DROP PROCEDURE IF EXISTS proc2;


DELIMITER //


CREATE PROCEDURE proc2()


BEGIN


SELECT empleados.nombre, empleados.apellido, empleados.dni, 
    camareros.encargado as Encargado, '' 
FROM empleados, camareros
WHERE empleados.codemple=camareros.codemple 
GROUP BY empleados.nombre
UNION
SELECT empleados.nombre, empleados.apellido, empleados.dni, '',
    cocineros.especialidad as Especialidad 
FROM empleados, cocineros
WHERE empleados.codemple=cocineros.codemple 
GROUP BY empleados.nombre;


END //


DELIMITER ;


call proc2();