MySQL Generate conditional result based on join

Question

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.

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.
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          |
|-------------|------------------|---------------------|-----------------|

If the department_id matches only one employee_no, then we shouldn't join the dept_location and the result of the department_location should be 'NA' (Not Applicable). The join to the dept_location is only valid when there are 2 or more non-unique employee_no. I hope this makes sense. Thank you! — user3376592
– user3376592, Commented Sep 4, 2019 at 0:16
Then it sounds like condition 2 is irrelevant and basically we should just output the department location if there is more than one distinct employee in that department? — Nick
– Nick, Commented Sep 4, 2019 at 0:17
@Nick Yes, you got it. If there is only one distinct employee, then we hardcode the department location to 'NA' (not applicable in this case) without doing any join. Sorry I was not precise enough. — user3376592
– user3376592, Commented Sep 4, 2019 at 0:26
@Nick Your solution worked great for me. Thanks to you and others for your time and effort. — user3376592
– user3376592, Commented Sep 4, 2019 at 1:08

Nick · Accepted Answer · 2019-09-04 00:30:18Z

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

LSerni · Accepted Answer · 2019-09-03 23:43:09Z

Not really sure if I understood, but it seems to me you can use a derived field, or at worst, an IF to choose between two different derivation formulas:

SELECT id, employee_no,       
    CASE (SELECT COUNT(*) FROM employees AS e WHERE e.department_id = employees.department_id)           
    WHEN 2 THEN 'CA' ELSE 'NA'      
END AS department_location,     
employee_manager FROM employees;

Test

CREATE TABLE department ( id integer, department_name varchar(30), dept_location varchar(30));
INSERT INTO department VALUES (1, 'Billing', 'CA'), (2, 'Marketing', 'NV');

CREATE TABLE employees (id integer, employee_no integer, department_id integer, employee_manager varchar(30));

INSERT INTO employees VALUES
(1, 34, 1, 'Robert'),
(2, 34, 1, 'Timothy'),
(3, 35, 1, 'John'),
(4, 36, 2, 'Benjamin'),
(5, 36, 2, 'Bryan');

Then the SELECT appears to be working:

SELECT id, employee_no,       CASE (SELECT COUNT(*) FROM employees AS e WHERE e.department_id = employees.department_id)           WHEN 2 THEN 'NA' ELSE 'CA'      END AS department_location,     employee_manager FROM employees;
+------+-------------+---------------------+------------------+
| 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            |
+------+-------------+---------------------+------------------+
5 rows in set (0.00 sec)

GMB · Accepted Answer · 2019-09-03 23:44:11Z

Here is a solution that relies on window functions (available in MySQL 8.0), which avoids the need of a subquery.

The trick is to compare the minimum and maximum employee number for each department. If they differ, then we know for sure that more than one distinct employee belongs to the given department.

SELECT
    e.id,
    e.employee_no,
    CASE 
        WHEN MAX(e.employee_no) OVER(PARTITION BY d.id) 
            = MIN(e.employee_no) OVER(PARTITION BY d.id)
        THEN 'NA'
        ELSE 'CA'
    END department_location,
    e.employee_manager
FROM employee e
INNER JOIN department d ON e.department_id = d.id
ORDER BY e.id

This demo on DB Fiddle with your sample data returns:

| 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                  |

Collectives™ on Stack Overflow

MySQL Generate conditional result based on join

3 Answers 3

Comments

Test

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Test

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related