I'm trying to learn using Dapper instead of EF Core because I want to learn the basics of SQL first. So far I have learned at a steady pace but now I'm stuck with the following.
I have 2 Tables, Employees and Managers.
One employee can have more than 1 manager in our firm. So that is the reason why I have to use 2 tables for the many to many relations and since a manager is also an employee, I only need two tables.
So in the Managers table, we have 2 foreign keys:
ManagerId (references back to the Employees table)
EmployeeId (references back to the Employees table)
Obviously, the name of the manager is not in that table, since we should be able to get it from the Employees table.
I can write the query to get the users and their manager id with an inner join, that works.
SELECT emp.EmailAddress, man.ManagerId
FROM Employees emp
LEFT JOIN Managers man ON emp.EmployeeId = man.EmployeeId
So far so good, but... what I actually want to see is the Managers name. Most examples I see online are mostly with a 1 emp has 1 manager query style, but now when you have a many to many like in my case.
So what is the best solution that I can actually yet again go back to Employees table to get the Managers name?
I hope my question is clear enough, thank you for the help!
In order to get the manager's details you need to join again on Employees. Something like this:
SELECT emp.EmailAddress, man.ManagerId, manDetails.Name
FROM Employees emp
LEFT JOIN Managers man ON emp.EmployeeId = man.EmployeeId
LEFT JOIN Employees manDetails ON man.ManagerId = manDetails.EmployeeId