70251

Query an employee from two or more departments

Question:

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

Answer1:

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;

Answer2:

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.

Answer3:

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

Recommend

  • Remove dynamically inserted code
  • Jquery - Change Option Value After Selection
  • French dependency parsing using CoreNLP
  • One form and mulitple models
  • How to use `Me` in vb.net With…End With block
  • ImportError: No module names 'matplotlib' Python 3.3
  • cancel plane ticket before 4 hours of departure time if dep time is 08:00:00 AM then Passengers can
  • Node-waf: not found
  • Javascript Appending to 2-D Array
  • parse XML string .net
  • Apache Zeppelin 0.6.1: Run Spark 2.0 Twitter Stream App
  • cannot pass session variables
  • Segfault when writing to string allocated by db [assembly]
  • “Inverted” SQL SELECT - Find staff who didn't make cold calls between a date range
  • Cannot install Protractor 2.0.0 : ENOENT in bufferutil while node-gyp rebuild
  • How to parse array into tuple with aeson?
  • How to model hierarchical data types in Haskell?
  • Enum type not being mapped to DB table
  • sqoop incremental import append mode with timestamp column
  • how do add multi sql dependency to this code?
  • PyInstaller Tkinter window low resolution in App bundle, but not in app program
  • Delayed “rendering” of WPF/Silverlight Dependency Properties?
  • remove user from group Mac Os X (El Capitan)
  • ORA-01843: not a valid month
  • Select Earliest Order for a set of tests in a daterange SQL
  • NHibernate: Persist an object with children
  • SQL multiple SELECT query with xmlagg function- Data not pulled in the required fashion
  • how to delete a file's on uninstall the android application? [duplicate]
  • Sharepoint change Content Type in Sharepoint Designer using workflow
  • Save and retrieve an image file in LibGDX
  • Move an object (element) one step up with Javascript
  • Using Select and where statement in Criteria
  • Getting data from database using HQL
  • How to merge two Request in Laravel
  • Geom_jitter colour based on values
  • How to write order and limit within cakephp joins array
  • Django: Count of Group Elements
  • How to check if every primary key value is being referenced as foreign key in another table
  • How to handle AllServersUnavailable Exception
  • How to get next/previous record number?