Connect columns from different tables

Question

SELECT e.EMPLOYEE_ID AS 'ID' 
    ,e.FIRST_NAME + ',' + e.LAST_NAME AS 'Name'
    ,(SELECT DEPARTMENT_NAME FROM HR.DEPARTMENTS WHERE HR.EMPLOYEES.DEPARTMENT_ID = DEPARTMENT_ID) AS 'Department Name'
    ,e.SALARY AS 'Base Salary'
FROM [HR].[EMPLOYEES] e
WHERE e.COMMISSION_PCT IS NOT NULL AND e.SALARY IS NOT NULL
UNION
SELECT e.EMPLOYEE_ID AS 'ID' 
    ,e.FIRST_NAME + ',' + e.LAST_NAME AS 'Name' 
    ,(SELECT DEPARTMENT_NAME FROM HR.DEPARTMENTS WHERE HR.EMPLOYEES.DEPARTMENT_ID = DEPARTMENT_ID) AS 'Department Name'
    ,e.SALARY AS 'Base Salary'
FROM [HR].[EMPLOYEES] e
WHERE e.COMMISSION_PCT IS NULL AND e.SALARY IS NOT NULL
ORDER BY e.SALARY DESC;

UPDATE

SELECT e.EMPLOYEE_ID AS 'ID' 
    ,e.FIRST_NAME + ',' + e.LAST_NAME AS 'Name'
,(SELECT DEPARTMENT_NAME FROM HR.DEPARTMENTS d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID) AS 'Department Name'
,(SELECT JOB_ID FROM HR.JOBS k WHERE e.JOB_ID = k.JOB_ID) AS 'Job ID'
,MAX(e.SALARY)AS 'Base Salary'
FROM HR.EMPLOYEES e
WHERE e.SALARY IS NOT NULL
GROUP BY e.SALARY
ORDER BY e.SALARY DESC

When I try to get the name of the department that is in HR.DEPARTMENTS, and try to match the DEPARTMENT_ID of the two tables, gives me the following error:

The multi-part Identifier "HR.EMPLOYEES.DEPARTMENT_ID" could not be bound.

How can I solve?

Answer 1

Try this:

SELECT e.EMPLOYEE_ID AS 'ID' 
    ,e.FIRST_NAME + ',' + e.LAST_NAME AS 'Name'
    ,(SELECT DEPARTMENT_NAME FROM HR.DEPARTMENTS d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID) AS 'Department Name'
    ,e.SALARY AS 'Base Salary'
FROM HR.EMPLOYEES e
WHERE e.COMMISSION_PCT IS NOT NULL AND e.SALARY IS NOT NULL
UNION
SELECT e.EMPLOYEE_ID AS 'ID' 
    ,e.FIRST_NAME + ',' + e.LAST_NAME AS 'Name' 
    ,(SELECT DEPARTMENT_NAME FROM HR.DEPARTMENTS d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID) AS 'Department Name'
    ,e.SALARY AS 'Base Salary'
FROM HR.EMPLOYEES e
WHERE e.COMMISSION_PCT IS NULL AND e.SALARY IS NOT NULL
ORDER BY e.SALARY DESC;

So you call the same reference to HR.EMPLOYEES, which you called "and" in "FROM", also placing an identifier for DEPARTMENTS.

Also, remove brackets of caller identifiers:

https://docs.microsoft.com/pt-br/sql/relational-databases/databases/database-identifiers?view=sql-server-2017

Now a question, are you sure you need to make a Union for this query?

You’re not doing the same search twice?

UPDATE

To improve the query, try this way:

SELECT e.EMPLOYEE_ID AS 'ID' 
    ,e.FIRST_NAME + ',' + e.LAST_NAME AS 'Name'
    ,(SELECT DEPARTMENT_NAME FROM HR.DEPARTMENTS d WHERE e.DEPARTMENT_ID = d.DEPARTMENT_ID) AS 'Department Name'
    ,e.SALARY AS 'Base Salary'
FROM HR.EMPLOYEES e
WHERE e.SALARY IS NOT NULL

So it will bring as you said it wants in your comment, null or not null. Whatever. Then you do ORDER BY as you want.