effective query than AND operator in SQL

April 20, 2022

iam using oracle database.the below query is not working as expected.

select * 
from cst_cust_attributes 
where attribute_value='event' 
and attribute_value='reg'
and attribute_value != 'guest';

i need all the customer data who has only attribute_values in both ‘event’ and ‘reg’.
And not in ‘guest’.but iam getting the correct output.the records consist of mixed of customer data who are having all 3 attribute_values.

below is structure of the table

Name            Null?    Type          
--------------- -------- ------------- 
ORGANIZATION_ID NOT NULL NUMBER(19)    
CUST_ID         NOT NULL VARCHAR2(32)  
ATTRIBUTE_ID    NOT NULL NUMBER(19)    
ATTRIBUTE_SEQ   NOT NULL NUMBER(10)    
ATTRIBUTE_VALUE NOT NULL VARCHAR2(254) 
ACTIVE_FLAG     NOT NULL NUMBER(3)     
CREATE_DATE              DATE          
CREATE_USER              VARCHAR2(254) 
UPDATE_DATE              DATE          
UPDATE_USER              VARCHAR2(254)

>Solution :

You can use the COUNT analytic function and conditional aggregation:

SELECT *
FROM   (
  SELECT a.*,
         COUNT(CASE attribute_value WHEN 'event' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_event,
         COUNT(CASE attribute_value WHEN 'reg' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_reg,
         COUNT(CASE attribute_value WHEN 'guest' THEN 1 END)
           OVER (PARTITION BY cust_id) AS num_guest 
  FROM   cst_cust_attributes a
)
WHERE  num_event > 0
AND    num_reg   > 0
AND    num_guest = 0;

Which, for the sample data:

INSERT INTO cst_cust_attributes (
  organization_id,
  cust_id,
  attribute_id,
  attribute_seq,
  attribute_value,
  active_flag
)
SELECT 1, 'C1', 1, 1, 'event', 1 FROM DUAL UNION ALL
SELECT 1, 'C1', 2, 1, 'reg',   1 FROM DUAL UNION ALL
SELECT 1, 'C1', 3, 1, 'guest', 1 FROM DUAL UNION ALL
SELECT 1, 'C2', 1, 1, 'event', 1 FROM DUAL UNION ALL
SELECT 1, 'C2', 2, 1, 'reg',   1 FROM DUAL UNION ALL
SELECT 1, 'C3', 1, 1, 'event', 1 FROM DUAL;

Outputs:

ORGANIZATION_ID CUST_ID ATTRIBUTE_ID ATTRIBUTE_SEQ ATTRIBUTE_VALUE ACTIVE_FLAG CREATE_DATE CREATE_USER UPDATE_DATE UPDATE_USER NUM_EVENT NUM_REG NUM_GUEST

1 C2 1 1 event 1 NULL NULL NULL NULL 1 1 0

1 C2 2 1 reg 1 NULL NULL NULL NULL 1 1 0