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.

开发者_Python百科

• 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

---

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

---

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;