Jump to content
684425

Query for records in same table

Recommended Posts

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

Capture.JPG

Share this post


Link to post
Share on other sites

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;

 

  • Like 1

Share this post


Link to post
Share on other sites

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, 

 

Share this post


Link to post
Share on other sites
1 hour ago, benanamen said:

code gymnastics

The ghost of Jacques still walks these corridors :)

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
Posted (edited)
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 by 684425

Share this post


Link to post
Share on other sites
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 🙂

Share this post


Link to post
Share on other sites
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.

  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.