Connect columns from different tables

Question

Connect columns from different tables

Navigation

#1 by (1 votes)

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 I try to match the DEPARTMENT_ID of the two tables, it gives me the following error:

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

How can I resolve?

sql-server

asked by anonymous 21.10.2018 / 19:09

1 answer

I can not get the view parameter in the controller Manipulation and grid, hbox and vbox in pyqt4

score 1 · Accepted Answer

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 called "and" in "FROM", also putting an identifier for DEPARTMENTS.

In addition, it removes the brackets of call identifiers:

link

Now a question, are you sure you need to join this query?

Are you not doing the same search twice?

UPDATE

To improve the query, try the following:

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 make ORDER BY as you wish.