Jump to content

Query for records in same table


684425

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

Link to comment
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, 

 

Link to comment
Share on other sites

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
Link to comment
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 🙂

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

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.