Structuing an sql query with join and case-CodePudding

I've stuck on how to properly structure my query. I need to have three columns: Name and Surname, Whether manager has premium or not(True or false), Department where manager works.

SELECT CONCAT(CONCAT(FIRST_NAME, ' '), LAST_NAME) AS "Name And Surname", DEPARTMENTS.DEPARTMENT_NAME, COMMISSION_PCT
LEFT JOIN HR.DEPARTMENTS ON EMPLOYEES.MANAGER_ID=DEPARTMENTS.MANAGER_ID
CASE
    WHEN COMMISSION_PCT IS NULL THEN 'False' ELSE 'True'
END
from HR.EMPLOYEES

I need to have three columns: Name and Surname, Whether manager has premium or not(True or false), Department where manager works. I know that "from" keyword should be after Select when i join, but in this case i dont know how to deal with it

CodePudding user response：

I guess like this (I don't have HR schema to test it).

Instead of nested concat, consider using the double pipe || concatenation operator.

SELECT e.first_name || ' ' || e.last_name AS "Name And Surname",
       d.department_name,
       CASE WHEN e.commission_pct IS NULL THEN 'False' ELSE 'True' END commission
  FROM employees e JOIN departments d ON d.manager_id = e.manager_id;