Effective query than AND operator in SQL

Question

I am 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 I am 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) 

Answer 1

you need to use a query like below which uses exists to check for all conditions

select * 
from cst_cust_attributes c
left join cst_mail n on c.CUST_ID   =n.CUST_ID   
where c.attribute_value='event' 
and exists 
( 
select 1 from cst_cust_attributes b where c.CUST_ID   = b.CUST_ID  
and b.attribute_value='reg'
)
and not exists
(
select 1 from cst_cust_attributes a where c.CUST_ID   = a.CUST_ID  
and a.attribute_value = 'guest'
)

Answer 2

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

could you please tell me where to add the below condition and trunc(CREATE_DATE) >='05-MAY-2019' AND trunc(CREATE_DATE)<='13-APR-2022' group by cust_id;

Adding a GROUP BY does not make sense if you are trying to return all the rows.

As for the date range, it depends on whether you want to check if:

• event and reg and not guest values are only within that range; or
• any event and reg and not guest rows exists, either inside or outside that date range, and then return the rows that are within that range.

For the former, the you would add a WHERE clause inside the sub-query:

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  create_date >= DATE '2019-05-05'
  AND    create_date <  DATE '2022-04-13' + INTERVAL '1' DAY
)
WHERE  num_event > 0
AND    num_reg   > 0
AND    num_guest = 0;

For the latter, the filters would be added to the outer query:

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
AND    create_date >= DATE '2019-05-05'
AND    create_date <  DATE '2022-04-13' + INTERVAL '1' DAY;

Which, for the sample data:

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

Then the former query returns the C2 and C4 rows and the latter query returns only the C2 rows (as a reg value exists for C4 but it is outside of the date range).

db<>fiddle here

---

I want join the column MAIL_ID from another table cst_mail using joins. to refer the corresponding maild id for the cust_id

Something like:

SELECT a.*,
       m.mail_id
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  create_date >= DATE '2019-05-05'
         AND    create_date <  DATE '2022-04-13' + INTERVAL '1' DAY
       ) a
       JOIN cst_mail m
       ON (a.cust_id = m.cust_id)
WHERE  num_event > 0
AND    num_reg   > 0
AND    num_guest = 0;

Answer 3

So you basically just want attribute_value to be "event" and "reg" ? So why just dont use the following select ?

select * from cst_cust_attributes where attribute_value in('event','reg');