Below is the data in CUST_REF table
CUST ID REF ID
1 7
2 2
3 5
4 5
5 5
Below query will return 3 5 and 4 5
SELECT CUST_ID, REF_ID
FROM CUST_REF
WHERE REF_ID IN
(select ref_id from CUST_REF
group by ref_id
having (count(*) > 1))
AND CUST_ID != REF_ID;
How about if want to return 1 7 2 2 5 5? I make below query it only able to return 1 7 and 2 2
SELECT CUST_ID, REF_ID
FROM CUST_REF
WHERE CUST_ID = REF_ID
AND REF_ID NOT IN
(select ref_id from CUST_REF
group by ref_id
having (count(*) > 1))
UNION
SELECT CUST_ID, REF_ID
FROM CUST_REF
WHERE CUST_ID != REF_ID
AND REF_ID NOT IN
(select ref_id from CUST_REF
group by ref_id
having (count(*) > 1));
It seems that you want to select rows whose REF_ID isn't duplicated, as well as rows whose REF_ID = CUST_ID. If that's so, how about this?
SQL> with cust_ref(cust_id, ref_id) as
2 (select 1, 7 from dual union
3 select 2, 2 from dual union
4 select 3, 5 from dual union
5 select 4, 5 from dual union
6 select 5, 5 from dual
7 )
8 select cust_id, ref_id
9 from cust_ref
10 where ref_id not in (select ref_id
11 from cust_ref
12 group by ref_id
13 having count(*) > 1
14 )
15 or ref_id = cust_id;
CUST_ID REF_ID
---------- ----------
1 7
2 2
5 5
SQL>