-1

I am having trouble wrapping my head around the on statement when doing a self-join. Let's say we have the following table:

employeeid name managerid salary
1 Mike 3 35000
2 Rob 1 45000
3 Todd NULL 25000
4 Ben 1 55000
5 Sam 1 65000

I want to perform a self join to return the employee name and their manager's name.

When I perform the following self join I get an incorrect result:

SELECT E.name as Employee,M.name as Manager
FROM tblEmployees E
LEFT JOIN tblEmployees M
ON E.Employeeid=M.managerid

However, when I reverse the columns on the on statement using the query below:

SELECT E.name as Employee,M.name as Manager
FROM tblEmployees E
LEFT JOIN tblEmployees M
ON E.managerid=M.Employeeid

I get the correct answer.

Why? How do I know which columns to select in an on statement?

philipxy
  • 14,867
  • 6
  • 39
  • 83
LarchPhi7
  • 1
  • 1
  • 1
    The order of the columns in an equality predicate does not matter. `ON E.Employeeid=M.managerid` is identical to `ON M.managerid=E.Employeeid`. However, you also switched the column names in the second query to the correct ones. – Dan Guzman Dec 16 '22 at 02:58
  • That clears up my question. Looks like I was using the wrong look up values. Do you know of any resources that explain choosing the correct look up values? – LarchPhi7 Dec 16 '22 at 03:16
  • I don't thin you'll find anything because this relies on you knowing your data. In this instant you need to understand that it is the Employee's Manager ID that is used to link to the Manager's Employee ID record. This will obviously change with every situation – RickyTillson Dec 16 '22 at 08:21
  • What is your 1 (specific researched non-duplicate) quesiton? Debug questions require a [mre]. [ask] [help] [research effort](https://meta.stackoverflow.com/q/261592/3404097) PS [CROSS JOIN vs INNER JOIN in SQL](https://stackoverflow.com/a/25957600/3404097) [Is there any rule of thumb to construct SQL query from a human-readable description?](https://stackoverflow.com/a/33952141/3404097) – philipxy Dec 16 '22 at 19:40
  • (Clearly,) These questions are faqs. Please before considering posting: Pin down code issues via [mre]. Read the manual/reference & google any error message & many clear, concise & precise phrasings of your question/problem/goal, with & without your particular names/strings/numbers, 'site:stackoverflow.com' & tags; read many answers. Reflect research in posts. SO/SE search is poor & literal & unusual, read the help. Google re googling/searching, including Q&A at [meta] & [meta.se]. [ask] [Help] – philipxy Dec 16 '22 at 19:47

1 Answers1

0

Here's my explanation:

  1. The table you have is structured with each row representing an employee in the company.

  2. You are interested in determining who is each employee's manager.

  3. You are able to find that by joining the table on itself where the lookup values are the manager ids (managerid) and the reference column are the employee ids (employeeid).

  4. The first query is wrong because the employeeid column is being used for the lookup values and the managerid column is being used for reference.

  5. To get the manager of each employee you need to look use the managerid column as the lookup column and the employeeid column as the reference column.

Hope that's not too confusing!

Bensstats
  • 988
  • 5
  • 17