We help IT Professionals succeed at work.

emaplyees with salary greater than another emplyee across multiple departments.

metropia
metropia asked
on
I need to join two tables EMP and DEPT
to find out how many workers in each dept have greater salary than emp_id 1, excluding dept_id 42.

tables and structures:



EMP

NAME
EMP_ID (PK)
DEPT_ID
SALARY

DEPT

NAME
DEPT_ID (PK)

Comment
Watch Question

Is this a homework question?

If so ... I can give some pointers ... but can't write the query for you.  If you give me your query so far, I can suggest corrections.

Author

Commented:
the query that i have chooses all employees whose salary is greater than emplyee number 13

but that is within the same table.

i need to join to dept table and exclude the sales dept.

SELECT NAME
FROM EMP
WHERE SALARY > (SELECT SALARY FROM EMP WHERE EMP_ID = 13)

Author

Commented:
I changed that one to this one:

SELECT DEPT.NAME, COUNT(EMP.NAME)
FROM EMP
JOIN DEPT
ON DEPT.DEPT_ID = EMP.EMP_ID
WHERE EMP.SALARY > (SELECT SALARY FROM EMP WHERE EMP_ID = 13)

But I am not sure how to exclude department id 42 from the results
Taking your last query, append:


AND DEPT.DEPT_ID <> 42

Author

Commented:
from the subquery, or the main query?

thanks.
to the main query
SELECT DEPT.NAME, COUNT(EMP.NAME)
FROM EMP
JOIN DEPT
ON DEPT.DEPT_ID = EMP.EMP_ID
WHERE EMP.SALARY > (SELECT SALARY FROM EMP WHERE EMP_ID = 13)
AND DEPT.DEPT_ID <> 42

Open in new window

@DanielWilson : A correction in your query. You joined on DEPT_ID of DEPT table to EMP_ID of EMP table. Which is wrong. The query should be like this :

SELECT DEPT.NAME, COUNT(EMP.NAME)
FROM EMP
JOIN DEPT
ON DEPT.DEPT_ID = EMP.DEPT_ID
WHERE EMP.SALARY > (SELECT SALARY FROM EMP WHERE EMP_ID = 13)
AND DEPT.DEPT_ID <> 42
Thanks, VipulKadia, you're right!

Author

Commented:
Thank you to both of you guys!

Explore More ContentExplore courses, solutions, and other research materials related to this topic.