effective query than AND operator-CodePudding

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

CodePudding user response：

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.

As noted in comments, no single row in this table can have more than one value for the column so you need to look for rows with any of the three values - with or or in.

The aggregation then counts how many of those values were found for each customer (see the first query in the db<>fiddle), which will include any customers with any of those three.

Finally the having clause filters out any of those customers who had fewer than three of the values. If the count is 3 for a customer then they had three rows in the table which matched the values you were looking for.

(This also assumes they can only have each attribute value once - if there could be duplicates within a customer then that could be handled by modifying what is counted to HAVING count(distinct attribute_value) = 3 - db<>fiddle with duplicates.)

i need join another column mail_id from another table cst_mail

You could just add a join in the main query:

SELECT cca.cust_id, cm.mail_id
FROM Cst_Cust_Attributes cca
LEFT JOIN cst_mail cm ON cm.cust_id = cca.cust_id
WHERE cca.attribute_value IN ('REG', 'GUEST', 'EVENT')
GROUP BY cca.cust_id
HAVING count(distinct cca.attribute_value) = 3

Or turn that into an inline view (or CTE) and join to that:

SELECT tmp.cust_id, cm.mail_id
FROM (
  SELECT cust_id
  FROM Cst_Cust_Attributes
  WHERE attribute_value IN ('REG', 'GUEST', 'EVENT')
  GROUP BY cust_id
  HAVING count(distinct attribute_value) = 3
) tmp
LEFT JOIN cst_mail cm ON cm.cust_id = tmp.cust_id