MySQL, single table multiple columns

Question

I have a table employees which has this structure:

ID | firstName | lastName | reportsTo | jobTitle |
--------------------------------------------------

I want to do a single query, which will show me firstName, lastName of employee and which employee He/She does report to (in this column, it contain ID of the employee).

So example of table data:

ID | firstName | lastName | reportsTo | jobTitle   |
----------------------------------------------------
35 | John      | Green    | 36        | TeamLeader |
----------------------------------------------------
36 | Annie     | Red      | null        | Supervisor |
----------------------------------------------------

So John Green has ID 35 and he reports to Annie Red. How to do this in a query?

So far I have come with basic:

SELECT ID, firstName, lastName from employees;

Thanks

Fabio · Accepted Answer · 2014-03-08 19:34:55Z

2

Try with a subquery

SELECT ID, firstName, lastName,
( 
    SELECT a.firstName
    FROM employees a
    WHERE a.ID = reportsTo
) as report
from employees;

answered Mar 8, 2014 at 19:34

Fabio

23.5k12 gold badges57 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

nifelvind Over a year ago

thanks for quick answer unfortunately. The one below is right, this puts null everywhere in report column.

Mihai · Accepted Answer · 2014-03-08 19:33:26Z

0

SELECT e.ID, e.firstName, e.lastName,ee.firstName, ee.lastName from employees e
JOIN employees ee
ON e.reportsTo=ee.ID

The name of the employee and the name which he reports to.

answered Mar 8, 2014 at 19:33

Mihai

26.8k8 gold badges71 silver badges87 bronze badges

Comments

Avt · Accepted Answer · 2014-03-08 19:37:17Z

0

Use

SELECT A.firstName, A.lastName, B.firstName 'reports to first name', 
B.lastName 'reports to last name'
from employees A JOIN employees B
ON B.ID = A.reportsTo

You can check it here http://sqlfiddle.com/#!2/22e33f/3

answered Mar 8, 2014 at 19:37

Avt

17.1k4 gold badges54 silver badges73 bronze badges

Collectives™ on Stack Overflow

MySQL, single table multiple columns

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related