CODEWITHSUNDEEP
sqldatabaseoracle-database
bn60
bn60

Reputation: 139

Query an employee from two or more departments

I'm having an error when executing the query. I don't know what seems to be the problem. I'm trying to query the DB to to find out the staffs from two or more departments. (ie. Staff_ID = 1, works under the DEPT_ID = 4, and 6). There are three tables involved:

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

Here's what I've done,

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

Upvotes: 0

Views: 1069

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269693

Here is your original query:

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 doing an aggregation on staff_id, but you have the columns staff_name and dept_name in your aggregation. You are looking for staff in multiple departments. You can get one row 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;

Notice: I've adding list_agg() in the select and s.staff_name in the group by.

If you want one person/department per row, then 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;

Upvotes: 2

cyborg007
cyborg007

Reputation: 123

Try this one:

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 have stored the staff_id's having count more than 1 in a temporary view and used it in the final select query.

As I have previously mentioned to techdo that you cannot group by sd.STAFF_ID || ' ' || s.STAFF_NAME, d.DEPT_NAME as it would always be unique and would have count as 1 always.

Upvotes: 1

TechDo
TechDo

Reputation: 18629

Please 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

Upvotes: 0

Related Questions