CODEWITHSUNDEEP
sqloracle-databaseoracle10g
tonyf
tonyf

Reputation: 35567

How to retrieve employees belonging to more than one Owner using Oracle SQL

I have a situation in Oracle where I have two tables - Table A and Table B.

Table B has a foreign key (aid) back to Table A.

Based on the above tables, I am trying to write a query where an emp_no in Table B belongs to more than one owner_id in Table A.

The rule of thumb is that an emp_no can only belong to just one owner_id.

Currently I have a data issues where one emp_no belongs to more than one owner_id.

For example, I have a situation where emp_no belongs to 2 or more owner_ids - it is these records that I need to retrieve back but unsure how to.

Information I would like to retrieve is:

Upvotes: 0

Views: 152

Answers (2)

Jeffrey Kemp
Jeffrey Kemp

Reputation: 60292

Adapting astander's answer to get both the emp_no and owner_id:

SELECT emp_no, owner_id
FROM (
SELECT  emp_no
       ,owner_id
       ,COUNT(DISTINCT owner_id) OVER (PARTITION BY emp_no) c
FROM    TableB b INNER JOIN 
        TableA a ON b.aid = a.aid 
)
WHERE c > 1;

Upvotes: 2

Adriaan Stander
Adriaan Stander

Reputation: 166476

You can try something like this

SELECT  emp_no,
        COUNT(DISTINCT owner_ID)
FROM    TableB b INNER JOIN
        TableA a ON b.aid = a.aid
GROUP BY emp_no
HAVING COUNT(DISTINCT owner_ID) > 1

Upvotes: 3

Related Questions