MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

CREATE TABLE employes_tbl(
        id  INT NOT NULL /*AUTO_INCREMENT*/ PRIMARY KEY ,
--        username VARCHAR(255) NOT NULL UNIQUE,
--        password VARCHAR(255) NOT NULL,
--        matricule CHAR(4) NOT NULL UNIQUE,
        role VARCHAR(255) NOT NULL ,
        firstName VARCHAR(255) NOT NULL,
--        lastname VARCHAR(255) NOT NULL,
--        email VARCHAR(255) NOT NULL,
--        cin VARCHAR(255) NOT NULL UNIQUE,
--        date_em date NOT NULL,
        departement VARCHAR(255) NOT NULL
--        fonction VARCHAR(255) NOT NULL,
--        responsable VARCHAR(255) NOT NULL,
--        burreaux VARCHAR(255) NOT NULL,
--        post VARCHAR(255) NOT NULL ,
--        address VARCHAR(255) NOT NULL,
--        photo VARCHAR(255) NOT NULL,
--       phone_portable VARCHAR(255) NOT NULL UNIQUE,
--        phone_fix VARCHAR(255) NOT NULL UNIQUE,
--        phone_extenstion VARCHAR(255) NOT NULL UNIQUE
    );
INSERT INTO employes_tbl(id, role, firstName, departement)
  SELECT 1, 'not me', 'John', 'accounting' UNION ALL
  SELECT 2, 'me neither', 'Paul', 'accounting' UNION ALL
  SELECT 3, 'who, me?', 'George', 'accounting' UNION ALL
  SELECT 4, 'responsable', 'Ringo', 'accounting' UNION ALL
  SELECT 5, 'never', 'Bob', 'shipping' UNION ALL
  SELECT 6, 'responsable', 'Fred', 'shipping' UNION ALL
  SELECT 7, 'wise guy', 'Larry', 'sales' UNION ALL
  SELECT 8, 'nyuk-nyuk-nyuk', 'Curly', 'sales' UNION ALL
  SELECT 9, 'responsable', 'Moe', 'sales'
SELECT * FROM employes_tbl


id
role
firstName
departement


1
not me
John
accounting

2
me neither
Paul
accounting

3
who, me?
George
accounting

4
responsable
Ringo
accounting

5
never
Bob
shipping

6
responsable
Fred
shipping

7
wise guy
Larry
sales

8
nyuk-nyuk-nyuk
Curly
sales

9
responsable
Moe
sales


    CREATE TABLE departments(
        id INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
        department_name VARCHAR(255) NOT NULL); 
INSERT INTO departments(id, department_name)
  SELECT 1, 'accounting' UNION ALL
  SELECT 2, 'shipping' UNION ALL
  SELECT 3, 'sales'
SELECT * FROM departments


id
department_name


1
accounting

2
shipping

3
sales


CREATE OR REPLACE VIEW department_responsable_view AS
  SELECT d.id,
         d.department_name,
         e.id AS id_responsable_employee,
         e.firstName
    FROM departments d
    LEFT OUTER JOIN employes_tbl e
      ON e.departement = d.department_name AND
         e.role = 'responsable'
SELECT *
  FROM department_responsable_view


id
department_name
id_responsable_employee
firstName


1
accounting
4
Ringo

2
shipping
6
Fred

3
sales
9
Moe

id	role	firstName	departement
1	not me	John	accounting
2	me neither	Paul	accounting
3	who, me?	George	accounting
4	responsable	Ringo	accounting
5	never	Bob	shipping
6	responsable	Fred	shipping
7	wise guy	Larry	sales
8	nyuk-nyuk-nyuk	Curly	sales
9	responsable	Moe	sales