I have a DETAILS table having

detail_id, customer_id and type 

columns.

type can either be 'A' or 'B'.

customer can have both type.

Goal is to find customer of Type B ONLY.

i.e output should not give customer details having both type.

The query should give only c2 as output.

Please let me know If I can achieve this without using 2 select statements.

CodePudding user response：

Something along the following lines should do the trick:

SELECT
    d.*
FROM
    DETAILS d
WHERE
    type = 'B'
    AND NOT EXISTS (SELECT 1
                    FROM DETAILS d2
                    WHERE d.customer_id = d2.customer_id
                    AND d2.type = 'A')

CodePudding user response：

Try the following query, which aggregates the type_id:

SELECT
      customer_id ,
      listagg( type,'') within Group (order by type)  " type_id"   
from [tablename]
      group by customer_id
      having listagg( type,'') within Group (order by type) ='B'