sql query to fetch records with equal to and not equal to condition-CodePudding

I have to fetch the records from the customer table where customer gift_type is only "cash" but those same customers should not have signed for "coupon" and "discount".below is my query it's not giving the exact records. more than 1 gift_type also gets included. I also tried using subquery, still doesn't work.

select cust_id from customer where gift_type='cash' and gift_type != 'coupon' and gift_type != 'discount';

select cust_id 
from customer c1, customer c2 
where c1.gift_type='cash' and c2.gift_type != 'coupon' and c2.gift_type != 'discount';

Thanks in advance for the help.

CodePudding user response：

select c.*
 from customer as c
 where c.gift_type='cash'
 and not exists
(
   select 1 from customer as x
     where c.customer_id=x.customer_id
      and x.gift_type in('coupon','discount')
)

Hope, the above is suitable for you

CodePudding user response：

You can do it in a single table scan using:

SELECT *
FROM   (
  SELECT c.*,
         COUNT(CASE WHEN gift_type IN ('coupon', 'discount') THEN 1 END)
           OVER (PARTITION BY customer_id) AS num_invalid
  FROM   customer c
) c
WHERE  gift_type = 'cash'
AND    num_invalid = 0;

Which, for the sample data:

CREATE TABLE customer (customer_id INT, gift_type VARCHAR(10));

INSERT INTO customer(customer_id, gift_type)
SELECT 1, 'cash'     FROM DUAL UNION ALL
SELECT 1, 'coupon'   FROM DUAL UNION ALL
SELECT 2, 'cash'     FROM DUAL UNION ALL
SELECT 2, 'discount' FROM DUAL UNION ALL
SELECT 3, 'cash'     FROM DUAL;

Outputs:

CUSTOMER_ID GIFT_TYPE NUM_INVALID

3 cash 0

CUSTOMER_ID	GIFT_TYPE	NUM_INVALID
3	cash	0

Oracle db<>fiddle MySQL db<>fiddle

CodePudding user response：

You might want to use not in or not exists, as you want all customers which gift_type is cash, but at the same time those same customers must not exist in the table for the other gift types.

Option 1

select * from customer a
where a.gift_type = 'cash' and 
not exists 
( select 1 from customer c where a.customer_id = c.customer_id and c.gift_type in ('coupon' , 'discount' )
);

Option 2

select * from customer a
where a.gift_type = 'cash' and 
a.customer_id not in 
( select c.customer_id from customer c where a.customer_id = c.customer_id and 
  c.gift_type in ('coupon' , 'discount' )
);