I have a simple question: How can I use having Count(Distinct) in SQL (Oracle to be exact) to return only the rows where there are 2 column with same record but 1 column with different record.

Example

CA_ID   BA_ID   SA_ID       
----    -----   --------    
CA1     BA1     SA1 
CA1     BA2     SA1 
CA1     BA2     SA2
CA1     BA3     SA1 
CA2     BA4     SA3
CA2     BA4     SA4
CA2     BA5     SA4
CA3     BA6     SA6

In that example, i'd like to create a query that shows 1 same CA_ID and SA_ID, but has different BA_ID. The query's result should show like this

CA_ID   BA_ID   SA_ID       
----    -----   --------    
CA1     BA1     SA1 
CA1     BA2     SA1 
CA1     BA3     SA1 
CA2     BA4     SA4
CA2     BA5     SA4

CodePudding user response：

We can try an aggregation approach to find the matching CA_ID and SA_ID values:

WITH cte AS (
    SELECT CA_ID, SA_ID
    FROM yourTable
    GROUP BY CA_ID, SA_ID
    HAVING MIN(BA_ID) <> MAX(BA_ID)
)

SELECT t1.*
FROM yourTable t1
WHERE EXISTS (
    SELECT 1
    FROM cte t2
    WHERE t2.CA_ID = t1.CA_ID AND
          t2.SA_ID = t1.SA_ID
);

CodePudding user response：

select  CA_ID   
       ,BA_ID   
       ,SA_ID
from   (select  t.*
               ,count(distinct BA_ID)  over(partition by CA_ID, SA_ID) as dcount
        from    t
       ) t
where  dcount > 1

Fiddle