Connect columns from different tables

Asked

Viewed 49 times

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?

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

  • 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

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

  • It works without UNION only so I’ll have two different result tables and I want it all in one

  • I put in the answer how you should make it work the way you said it. See if it helps ;) @Brunobacelar

  • 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

  • @Brunobacelar Adds at the end: ORDER BY and.SALARY DESC; which will work. =)

Show 2 more comments

Browser other questions tagged

You are not signed in. Login or sign up in order to post.