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

Question

Hi,

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

Table A has the following columns (aid, owner_id, app_id) and

Table B has the following columns (bid, aid, emp_no)

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:

emp_no and owner_id

Any help with query would be appreciated using Oracle sql.

Thanks.

Answer 1

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

Answer 2

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;