Self Joining between 2 same tables

For instance I have a table called employees where it consists of “Employee ID”, “First Name”, “Last Name”, “Manager ID”. To count the subordinate of each manager, I tried to self-joining between the 2 tables.

SELECT e1.first_name, e1.last_name, COUNT(e1.employee_id)
FROM employee e1 INNER JOIN e2 ON e1.employee_id = e2.manager_id
GROUP BY e1.first_name, e1.last_name   

Am I right? Also, if I want to join with other tables after self-joining, is the joining statement right?

FROM ((self-joining) INNER JOIN other tables ON "common column") 

Combining the first and last name:

SELECT CONCAT(e1.first_name,' ',e1.last_name) "Full Name", COUNT(e1.employee_id)
FROM employee e1 INNER JOIN e2 ON e1.employee_id = e2.manager_id
GROUP BY "Full Name" 

I can’t compile this….What is wrong?

Answer

An answer for your first question is just a minor tweak to your query:

SELECT e1.firstname AS managerFirstName, e1.lastname AS managerLastName, COUNT(e1.employeeid)
FROM employees e1 INNER JOIN employees e2 ON e1.employeeId = e2.managerId
GROUP BY e1.firstname, e1.lastname;

I really haven’t changed much here – and you can see that it works at: http://sqlfiddle.com/#!9/187477/1

The essential change is sticking with the names coming from the same table reference (e1) and GROUPing BY the same fields. You also need to (as commented) indicate the table name before aliasing with “e2”.

(Note that the aliasing of the names is just to help indicate that these are managers, it’s not an essential part of the query. Also, I used slightly different field names, but the logic is the same.)

As to your second question, I’d do it using the self-join query as a sub-query, more or less as you suggest. Try something out – you’re essentially at a solution.

EDIT IN RESPONSE TO QUESTION EDIT:

Adding the concatenation in (note that oracle has some limits around concatenating more than 2 strings, so this is one possible workaround – there’s more info at this answer: Oracle SQL, concatenate multiple columns + add text):

SELECT CONCAT(CONCAT(e1.firstname, ' '), e1.lastname) AS managerName, COUNT(e1.employeeid)
FROM employees e1 INNER JOIN employees e2 ON e1.employeeId = e2.managerId
GROUP BY e1.firstname, e1.lastname;

still works: http://sqlfiddle.com/#!4/b0cbcd/4