Simplify_SubQuery_With_Functions.md

CREATE TABLE departments (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	PRIMARY KEY(id)
);

CREATE TABLE employees (
	id INT NOT NULL AUTO_INCREMENT,
	name VARCHAR(255) NOT NULL,
	department_id INT NOT NULL,
	PRIMARY KEY(id),
	FOREIGN KEY (department_id) REFERENCES departments(id) ON DELETE CASCADE
);

INSERT INTO departments (name) VALUES ('Administration'), ('Sales');
INSERT INTO employees (name, department_id) VALUES ('John Doe', 1), ('Jane Doe', 1), ('Janie Doe', 2), ('Johnny  Doe', 2);


DROP FUNCTION IF EXISTS getDepartmentId;

DELIMITER $$

CREATE FUNCTION getDepartmentId(depName VARCHAR(255)) RETURNS INT

BEGIN
  DECLARE depId INT;
  SELECT id INTO depId FROM departments WHERE name = depName;
  RETURN depId;
END$$

DELIMITER ;


SELECT * FROM employees WHERE department_id = getDepartmentId('Sales');