Question 112708 in Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Query to find type of customer of type B ONLY

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.

detail_id customer_id type
0 c1 A
1 c2 B
2 c1 B
3 c3 A

The query should give only c2 as output.

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

Tags:

3

Answers


  1. 0

    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')
    Login or Signup to reply.
  2. 0

    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'

    To complete my Anwser. Here is the Code for mysql:

     SELECT
      customer_id ,
      group_concat( type,'') as " type_id"   
 from [Tablename]
      group by customer_id
      having group_concat( type,'') ='B'
    Login or Signup to reply.
  3. 0 
    SELECT d.*
FROM DETAILS d  
WHERE type = 'B'    
AND customer_id NOT IN 

                   (SELECT customer_id
                    FROM DETAILS
                    WHERE type = 'A');
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search