Self Joining between 2 same tables

Question

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?

That query wouldn't compile. There's the table name missing in the `JOIN` clause and `e2.last_name` isn't in the `GROUP BY` clause but in the list of columns without an aggregation function. Besides that, why don't you just try what you have in a small example and see if it gets you what you want? And yes, you can add other `JOIN` clauses the way you showed. But usually you don't need (and want) the parenthesis. — sticky bit, Dec 17 '19 at 04:02

score 2 · Answer 1 · answered Dec 17 '19 at 05:10

You will need to list all the columns in the group by if they are directly referenced in the SELECT clause.

Also, You can concat in the oracle using (||), CONCAT method can only accept two parameters in the oracle.

So your query should look like this:

SELECT
    -- concatanation using ||
    E1.FIRSTNAME
    || ' '
    || E1.LASTNAME AS MANAGERNAME, 
    COUNT(E1.EMPLOYEEID)
FROM
    EMPLOYEES E1
    INNER JOIN EMPLOYEES E2 ON E1.EMPLOYEEID = E2.MANAGERID
GROUP BY
    -- both of the columns are used in the select clause and must be used in the GROUP BY clause
    E1.FIRSTNAME, 
    E1.LASTNAME;

Cheers!!

Stidgeon · Accepted Answer · 2019-12-17T04:58:54.707

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

What if I want to combine first name and last name together, but oracle wouldn't allow....I'm unsure where did I go wrong? I will add to the above... — user234568, Dec 17 '19 at 04:23
I tried, but it wouldn't allow me to in the GROUP BY expression....stating that (FULL NAME) is an invalid identifier..... — user234568, Dec 17 '19 at 04:47

Self Joining between 2 same tables

2 Answers2