CODEWITHSUNDEEP
sqldatabase
user3353684
user3353684

Reputation: 21

SQL Join Two Tables on Column

There are two tables Department and Employee Department:

DID DName           PhoneExt 
ADM Administration  100 
CLT Client Services 101 
DAT Data Conversion 102 

EMPLOYEE
EID EName Salary   MID   DID 
e001 Martin 57000        CLT 
e002 West 39000   e001   CLT 
e003 Wilson 64000        DAT 
e004 Patel 48000  e003   DAT 
e005 Rae 42000    e001   CLT 
e006 Jones 36000  e003   DAT 
e007 Dunn 52000   e003   PRG

How can I list the names and ids of those employees whose manager is from the Client Services department?

Upvotes: 2

Views: 77

Answers (4)

Chains
Chains

Reputation: 13157

Using a subquery is fine (as suggested by Dour, Mohamed, Rafa, ...), however, you can do this more efficiently (without a subquery) by self-joining the employee table:

SELECT
   emp.Ename,
   emp.EID
FROM
   EMPLOYEE emp

   INNER JOIN EMPLOYEE mngr
   ON emp.MID = mngr.EID
WHERE
   mngr.DID = 'CLT'

Note -- you don't actually need to use the department table at all...

Upvotes: 0

Mohamed Abd El Raouf
Mohamed Abd El Raouf

Reputation: 928

You can use this query:

SELECT EID, EName FROM EMPLOYEE WHERE MID IN (SELECT EID FROM EMPLOYEE WHERE DID = 'CLT')

Upvotes: 0

Rafa Paez
Rafa Paez

Reputation: 4860

SELECT e1.* FROM employee e1 WHERE e1.MID IN 
  (SELECT e2.EID FROM employee e2 WHERE e2.DID = 'CLT')

Upvotes: 0

Dour High Arch
Dour High Arch

Reputation: 21712

List the names and ids:

SELECT EName, EID

of those employees:

FROM EMPLOYEE

whose manager is from:

WHERE MID IN

the Client Services department:

(SELECT EID FROM EMPLOYEE WHERE DID = 'CLT')

Upvotes: 2

Related Questions