I'm trying to make a list of employees working in a same department like:

But the only thing i can do for now is:

SELECT emp_name, dept_code
  FROM employee
 WHERE dept_code IN (SELECT dept_code FROM employee);

How can I make a list pairing with the employee working in a same department? thanks gurus...

CodePudding user response：

To first point that out: I dislike your idea to create such a result listing "pairs" twice and would prefer another, easier query whose results would be better to read. I will come back to this later in this answer.

But anyway, if you really want to produce the outcome you have shown, we can do this with CROSS JOIN. This builds all combinations of employees.

In the WHERE clause, we will set the conditions that they must work in the same department, but have different names:

SELECT 
e1.emp_name AS employeeName, 
e1.dept_code AS department, 
e2.emp_name AS employeeName
FROM 
employee e1
CROSS JOIN employee e2
WHERE 
e1.dept_code = e2.dept_code
AND e1.emp_name <> e2.emp_name
ORDER BY e1.dept_code, e1.emp_name, e2.emp_name;

To come back to the idea to make this much easier and better to read: We can just use LISTAGG with GROUP BY to produce a comma-separated list of employees per department. I highly recommend to use this approach due to much better performance and readability.

This query will do on new Oracle DB's:

SELECT dept_code, 
LISTAGG (emp_name,',') AS employees
FROM employee
GROUP BY dept_code;

On older Oracle DB's, we need to add a WITHIN GROUP clause:

SELECT dept_code, 
LISTAGG (emp_name,',') 
  WITHIN GROUP (ORDER BY emp_name) AS employees
FROM employee
GROUP BY dept_code;

This will produce following result for your sample data:

Here we can try out these things: db<>fiddle

CodePudding user response：

You will get all the pairs (A,B) and (B,A) of employees in the same department at the exclusion of all (A,A) with:

SELECT e1.emp_name AS first_emp_name, e1.dept_code, e2.emp_name AS second_emp_name
  FROM employee e1
JOIN employee e2 ON e1.dept_code = e2.dept_code AND e1.emp_name <> e2.emp_name ;