effective query than AND operator

April 20, 2022

I would like to know that is there any other effective way or query to get the desired output for the below query with AND operators because if I run this below query I am getting 0 counts. but according to the data warehouse report, there are many records for the below combination.

Any alternative way to search?

select Count(*)
FROM   Cst_Cust_Attributes
WHERE  ATTRIBUTE_VALUE = 'REG'
       AND ATTRIBUTE_VALUE = 'GUEST'
       AND ATTRIBUTE_VALUE = 'EVENT'

Thanks in advance

>Solution :

Assuming your table has a cust_id column, you seem to want something like:

SELECT cust_id, count(*)
FROM Cst_Cust_Attributes
WHERE attribute_value IN ('REG', 'GUEST', 'EVENT')
GROUP BY cust_id
HAVING count(*) = 3

That would list all customers which have all three attribute values. You could leave the count out of the column list if you don’t want to see that; it doesn’t need to be there, the important bit is having it in the having clause.

db<>fiddle with very basic made-up data.

(This also assumes they can only have each attribute value once – if there could be duplicates then that could be handled by modifying what is counted.)