mysql - Filter rows in SQL table if value exists in column for each group in a priority order

Question

I am trying to figure out a SQL query to join and filter out records based on values of 2 different columns. The filter needs to be applied based on following logic,

If an employee id has 'type' column with value as C, select the row and discard all other rows for the same employee id.

If there are no rows with 'type' column with value as C for a given employee, then check if any row for employee exists with value as D. If yes, then select the row where department is ADMIN. If no rows exist with Department as ADMIN, then check and select row with Department as HR and so forth

**Table A**

Employee Id| Employee Name|
--------------------------
123        | John Doe     |
456        | Jane Doe     |
789        | Jack Doe     |

**Table B**

Employee Id| Type| Department| Date      |
-----------------------------------------
123        | A   | ABC       | 09/09/2020|
123        | B   | ABC       | 09/09/2020|
123        | C   | ABC       | 09/09/2020|
789        | D   | FIN       | 09/09/2020|
789        | D   | HR        | 09/09/2020|
789        | D   | FIN       | 09/09/2020|
789        | D   | HR        | 09/09/2020|
789        | D   | ADMIN     | 09/09/2020|

**Output**

Employee Id| Type| Department| Date      | Employee Name|
--------------------------------------------------------
123        | C   | ABC       | 09/09/2020| John Doe     |
456        | null| null      | null      | Jane Doe     |
789        | D   | ADMIN     | 09/09/2020| Jack Doe     |

Thanks in advance.

标签： mysqlsql

Answer 1

I think you want:

select . . .
from a left join
     (select b.*,
             row_number() over (partition by employee_id 
                                order by (case when type = 'C' then 1
                                               when type = 'D' then 2
                                               . . .
                                          end)
                               ) as seqnum
      from b
     ) b
     on b.employee_id = a.employee_id and seqnum = 1;