0
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?
It worked! Yes, I need Union to run the query when the commission is not null and when it is null.Can you just explain what’d' and 'e' are for? I am new in SQL and still not quite solid these concepts
– Bruno Bacelar
@Brunobacelar, I get it. Try then, instead of UNION, remove the clause e.COMMISSION_PCT IS NOT NULL in the first one and run it without the query UNION below, I will put in the answer as it should do.
– NDelavi
It works without UNION only so I’ll have two different result tables and I want it all in one
– Bruno Bacelar
I put in the answer how you should make it work the way you said it. See if it helps ;) @Brunobacelar
– NDelavi
I don’t want to impose on your patience but I need help with a few more things, I just want to see who has the highest salary, I’ve updated the code, only it gives me a mistake
– Bruno Bacelar
@Brunobacelar Adds at the end: ORDER BY and.SALARY DESC; which will work. =)
– NDelavi
Let’s go continue this discussion in chat.
– Bruno Bacelar