CODEWITHSUNDEEP
mysql
Ilija Petkovic
Ilija Petkovic

Reputation: 143

Select name by id from another column with join another table

Huh, this is the simplest title that I could think of.

So this is my problem. I have two table (data for example): Table: 

EMPLOYEES
=====================================================
ID    NAME    SUPERVISOR    LOCATION    SALARY
-----------------------------------------------------
34       John                  AL          100000
17       Mike    34            PN          75000
5        Alan    34            LE          25000
10       Dave    5             NY          20000

And second table (data for example):

BONUS
========================================
ID        Bonus
----------------------------------------
17        5000
34        5000
10        2000

I have to select employee's name, supervisor's name and bonus of everyone who got a bonus greater than 1000. So I wrote this query:

SELECT e.name, e.supervisor, b.bonus from employees e join bonus as b on b.empid = e.empid where b.bonus > 1000

But I dont know how to replace supervisors ID with name to get this result:

RESULT:
=====================================
Name     Supervisor    Bonus
-------------------------------------
Dave     Alan          2000
John     -             5000
Mike     John          5000

Can you please help me? I'm really out of ideas...

Upvotes: 0

Views: 3179

Answers (2)

eggyal
eggyal

Reputation: 125835

You need to join against the employees table a second time; you can use aliases to distinguish between the two references to the same table:

SELECT e.name, s.name AS supervisor, b.bonus
FROM   employees AS e
  JOIN bonus     AS b USING (empid)
  LEFT JOIN employees AS s ON s.empid = e.supervisor
WHERE  b.bonus > 1000

See it on sqlfiddle.

A LEFT JOIN is required because you also wish to include employees who have no supervisor (e.g. John). See A Visual Explanation of SQL Joins for more information.

An alternative, less efficient (but sometimes easier to understand), way would be to use a correlated subquery:

SELECT e.name, (SELECT name FROM employees WHERE empid=e.supervisor), b.bonus
FROM   employees AS e
  JOIN bonus     AS b USING (empid)
WHERE  b.bonus > 1000

See it on sqlfiddle.

Upvotes: 1

Dimitri
Dimitri

Reputation: 1208

You have to join the table to itself:

SELECT e.NAME as EmployeeName, s.NAME as SupervisorName FROM EMPLOYEES e join EMPLOYEES s ON e.SUPERVISOR = s.ID

That will give you employees and supervisors. Go from there to get the employees meeting your criteria.

Upvotes: 1

Related Questions