what is the correct joins format?-CodePudding

SELECT e.last_name, e.hire_date 
FROM employees e 
JOIN employees  
ON (e.last_name = 'HUNOLD')

I should display the last name and hire date of any employee who works in the same department as HUNOLD using (ORACLE LIVE SQL), but got stuck, can anyone help in this?

Departments table:

    DEPARTMENT_ID   DEPARTMENT_NAME MANAGER_ID  LOCATION_ID
10  Administration  200 1700
20  Marketing   201 1800
30  Purchasing  114 1700
40  Human Resources 203 2400
50  Shipping    121 1500
60  IT  103 1400
70  Public Relations    204 2700
80  Sales   145 2500
90  Executive   100 1700
100 Finance 108 1700
110 Accounting  205 1700
120 Treasury     -  1700
130 Corporate Tax    -  1700
140 Control And Credit   -  1700
150 Shareholder Services     -  1700
160 Benefits     -  1700
170 Manufacturing    -  1700
180 Construction     -  1700
190 Contracting  -  1700
200 Operations   -  1700
210 IT Support   -  1700
220 NOC  -  1700
230 IT Helpdesk  -  1700
240 Government Sales     -  1700
250 Retail Sales     -  1700
260 Recruiting   -  1700
270 Payroll  -  1700

CodePudding user response：

Have a subquery to return Hunold's department.

SELECT e.last_name, e.hire_date 
FROM employees e
WHERE e.departmentid in (select e2.departmentid
                         from employees e2
                         where e2.last_name = 'HUNOLD')

You can also JOIN:

SELECT e.last_name, e.hire_date 
FROM employees e
JOIN employees e2 ON e.departmentid = e2.departmentid 
where e2.last_name = 'HUNOLD'

CodePudding user response：

You do not need any JOINs for this if you use analytic functions:

SELECT last_name,
       hire_date
FROM   (
  SELECT last_name,
         hire_date,
         COUNT(CASE last_name WHEN 'HUNOLD' THEN 1 END) OVER (
           PARTITION BY department_id
         ) AS number_of_hunolds
  FROM   employees
)
WHERE  number_of_hunolds > 0;

If you did want to use a sub-query then you can use EXISTS:

SELECT last_name,
       hire_date
FROM   employees e
WHERE  EXISTS (SELECT 1
               FROM   employees x
               WHERE  x.last_name = 'HUNOLD'
               AND    e.department_id = x.department_id);

or an INNER JOIN:

SELECT e.last_name,
       e.hire_date
FROM   employees e
       INNER JOIN employees e2
       ON (e.department_id = e2.department_id)
WHERE  e2.last_name = 'HUNOLD';