MySQL Generate conditional result based on join

I have 2 tables – department and employee.

Employee table: department_id is a foreign key from the department table (id column)

|-------------|------------------|---------------|-----------------|
|      id     | employee_no      | department_id |employee_manager |
|-------------|------------------|---------------|-----------------|
|       1     |      34          |    1          |  Robert         |
|       2     |      34          |    1          |  Timothy        |
|       3     |      35          |    1          |  John           |
|       4     |      36          |    2          |  Benjamin       |
|       5     |      36          |    2          |  Bryan          |
|-------------|------------------|---------------|-----------------|

Department table:

|-------------|------------------|---------------|
|      id     |  department_name | dept_location |
|-------------|------------------|---------------|
|       1     |   Billing        |    CA         |
|       2     |  Marketing       |    NV         |
|-------------|------------------|---------------|

I need help with a sql query that returns all rows in the employee table that matches the department_id in the department table with the following conditions.

1. If the department id matches more than one non-unique employee_no (for eg, department_id 1 matches employee_no 34 & 35), then the join should get the dept_location which is ‘CA’ from the department table.

2. If the department id matches unique employee_no even more than once (for eg, department_id 2 matches employee_no 36 twice), then the join from the department table is not applicable and the result should be ‘NA'(Not Applicable) for the dept_location

My result should look like the table below:

|-------------|------------------|---------------------|-----------------|
|      id     | employee_no      | department_location |employee_manager |
|-------------|------------------|---------------------|-----------------|
|       1     |      34          |   CA                |  Robert         |
|       2     |      34          |   CA                |  Timothy        |
|       3     |      35          |   CA                |  John           |
|       4     |      36          |   NA                |  Benjamin       |
|       5     |      36          |   NA                |  Bryan          |
|-------------|------------------|---------------------|-----------------|

Answer

Here’s a query that will work in versions of MySQL prior to 8.0. It uses a derived table of counts of distinct employees per department to determine whether to display the department location or NA:

SELECT e.id, e.employee_no, 
       CASE WHEN c.distinct > 1 THEN d.dept_location
       ELSE 'NA'
       END AS department_location,
       e.employee_manager
FROM employees e
JOIN (SELECT department_id, COUNT(DISTINCT employee_no) AS `distinct`
      FROM employees
      GROUP BY department_id) c ON c.department_id = e.department_id
JOIN department d ON d.id = e.department_id

Output:

id  employee_no employee_manager    department_location
1   34          Robert              CA
2   34          Timothy             CA
3   35          John                CA
4   36          Benjamin            NA
5   36          Bryan               NA

Demo on dbfiddle