Geek Answers Handbook

Request an employee from two or more departments

I got an error while executing the request. I do not know what the problem is. I am trying to query a database to find out employees from two or more departments. (i.e. Staff_ID = 1, works under DEPT_ID = 4 and 6). There are three tables:

1. STAFF 2. DEPARTMENT 3. STAFF_DEPT (contains ID of STAFF and DEPT)

Here is what I did

 SELECT sd.STAFF_ID || ' ' || s.STAFF_NAME AS "Staff Name", d.DEPT_NAME AS "Department" FROM STAFF_DEPT sd INNER JOIN STAFF s ON sd.STAFF_ID = s.STAFF_ID INNER JOIN DEPARTMENT d ON sd.DEPT_ID = d.DEPT_ID GROUP BY sd.STAFF_ID HAVING COUNT (sd.STAFF_ID) > 1
0
sql database oracle
source share
3 answers

Here is your original request:

 SELECT sd.STAFF_ID || ' ' || s.STAFF_NAME AS "Staff Name", d.DEPT_NAME AS "Department" FROM STAFF_DEPT sd INNER JOIN STAFF s ON sd.STAFF_ID = s.STAFF_ID INNER JOIN DEPARTMENT d ON sd.DEPT_ID = d.DEPT_ID GROUP BY sd.STAFF_ID HAVING COUNT (sd.STAFF_ID) > 1;

The problem with your query is that you are performing aggregation on staff_id , but you have the staff_name and dept_name in your aggregation. You are looking for employees in several departments. You can get one line per person with a list of departments using:

 SELECT sd.STAFF_ID || ' ' || s.STAFF_NAME AS "Staff Name", list_agg(d.DEPT_NAME, ',') within group (order by DEPT_NAME) AS "Department_List" FROM STAFF_DEPT sd INNER JOIN STAFF s ON sd.STAFF_ID = s.STAFF_ID INNER JOIN DEPARTMENT d ON sd.DEPT_ID = d.DEPT_ID GROUP BY sd.STAFF_ID, s.STAFF_Name HAVING COUNT (sd.STAFF_ID) > 1;

Note. I add list_agg() to select and s.staff_name to group by .

If you need one person / department for each row, use a subquery with an analytic function:

 selectsd.STAFF_ID || ' ' || s.STAFF_NAME AS "Staff Name", dept_name from (select sd.staff_id, s.staff_name, d.dept_name, count(*) over (partition by sd.staff_id, s.staff_name) as NumDepts FROM STAFF_DEPT sd INNER JOIN STAFF s ON sd.STAFF_ID = s.STAFF_ID INNER JOIN DEPARTMENT d ON sd.DEPT_ID = d.DEPT_ID ) t where NumDepts > 1;
+2
source share

Try the following:

 with temp as (select sd.staff_id from staff_dept sd group by staff_id having count(staff_id)>1) select tp.staff_id||' ' ||s.Name AS "Staff Name", d.DNAME FROM temp tp, staff_dept sd, staff s, dept d where tp.staff_id=sd.staff_id and sd.staff_id=s.id and sd.dept_id=d.deptno;

I saved staff_id with a score of more than 1 in a temporary submission and used it in the final selection request.

As I mentioned in techdo, you cannot group sd.STAFF_ID || '' || s.STAFF_NAME, d.DEPT_NAME, since it would always be unique and always be considered 1.

+1
source share

Try:

 SELECT sd.STAFF_ID || ' ' || s.STAFF_NAME AS "Staff Name", d.DEPT_NAME AS "Department" FROM STAFF_DEPT sd INNER JOIN STAFF s ON sd.STAFF_ID = s.STAFF_ID INNER JOIN DEPARTMENT d ON sd.DEPT_ID = d.DEPT_ID GROUP BY sd.STAFF_ID || ' ' || s.STAFF_NAME, d.DEPT_NAME HAVING COUNT (sd.STAFF_ID) > 1
0
source share

More articles:


All Articles