Query for records in same table

[684425]

I have an employees table which stores id as auto increment values, names of employees and boss id chosen from the same table. I want to run a query which shows result as

Hi Jane Doe, your BOSS is John Doe

But it seems beyond my knowledge. Any help please?

I have attached image of similar data

[Barand]

Join the table to itself

SELECT emp.name as empname
     , boss.name as bossname
FROM employee as emp
     LEFT JOIN
     employee as boss
ON emp.boss_id = boss.id;

 

[benanamen]

Op, you would do better to separate the first and last names into their own columns. A simple use case example for using only the last name would be the intro to a letter. As is, you would have to do some code gymnastics just to get the last name only.

Dear Mr. Doe, 

 

[Barand]

1 hour ago, benanamen said:

code gymnastics

The ghost of Jacques still walks these corridors

[benanamen]

6 hours ago, Barand said:

The ghost of Jacques still walks these corridors

LOL, that's exactly where I got that from. He really has been a ghost. Hasn't shown up anywhere.

[684425]

19 hours ago, Barand said:

Join the table to itself

SELECT emp.name as empname
     , boss.name as bossname
FROM employee as emp
     LEFT JOIN
     employee as boss
ON emp.boss_id = boss.id;

 

Sir, your query worked as expected but last day after posting my problem here i searched for the solution. From w3schools i found one example in SELF JOIN.

SELECT A.fname AS yourname,
       B.fname AS yourbossname
FROM employee A,
     employee B
WHERE A.boss_id = B.id

Not challenging your knowledge and your experience but just for increasing my knowledge. Do both LEFT JOIN and SELF JOIN are same in working or is there any difference in both?

PS: if its up to you, which one you would like to prefer?🙂

Edited May 30, 2020 by 684425

[684425]

16 hours ago, benanamen said:

Op, you would do better to separate the first and last names into their own columns. A simple use case example for using only the last name would be the intro to a letter. As is, you would have to do some code gymnastics just to get the last name only.

Dear Mr. Doe, 

 

I was not sure whether i should separate them or not, but after your advice i separated them 🙂

[Barand]

6 hours ago, 684425 said:

Not challenging your knowledge and your experience but just for increasing my knowledge. Do both LEFT JOIN and SELF JOIN are same in working or is there any difference in both?

Use explicit joins

FROM A JOIN B ON ...

rather than confusing the issue by putting the join conditions in a WHERE clause (less efficient too).

Also there may be employee/s at the top of the management tree without a manager.

With my LEFT JOIN these would still appear, with w3fools's version they would not.