Last answered:

15 Nov 2022

Posted on:

07 Dec 2021

1

Some employees has more than one emp_no?

My code turns out to be exactly the same as the answer.

But when I try to test my code with emp_no: 10011 employee Georgi Facello, it returns an error saying results consist of more than one row.

I dig deeper and realise that Georgi Facello has two emp_no in the employee table, which is 10011 and 55649.

Upon these cases how can I write my code to find out the two different Georgi Facello then?

Thanks

1 answers ( 0 marked as helpful)
Posted on:

15 Nov 2022

0

Hello, I did the same test as you and the result was the same.
So I added the MAX function in the code. I am not sure if is it right but it works :D

DROP PROCEDURE IF EXISTS emp_info;
DELIMITER $$
CREATE PROCEDURE emp_info(IN p_first_name VARCHAR(255), IN p_last_name VARCHAR(255),out p_emp_no INTEGER)
BEGIN
SELECT
    MAX(e.emp_no)
INTO p_emp_no
FROM
    employees e
WHERE
    e.first_name = p_first_name AND e.last_name=p_last_name;
END$$

Submit an answer