CODEWITHSUNDEEP
sqlpostgresqlquery-optimization
Adnan
Adnan

Reputation: 195

SQL query with multiple OR condition inside AND returns null or empty

I'm trying to get those members who are going to match my filter criteria, but I am passing multiple OR condition inside the round brackets, and every condition which is inside the round brackets is, AND with another round bracket; however, the query does not work, and it returns an empty table, but whensoever I run the query with INTERSECT statement it returns the members. Still, it has been taking 3 seconds on 1 million records, and it is a very cost-paying operation on the server-side, and it will increase if the records increase. Could someone help me why my first query is not working or if I use the second query, is there any way to attenuate the time?

My actual table enter image description here

First Query, which returns Empty table

SELECT custom_attribute_values.attributable_id as Member_id FROM custom_attribute_values
WHERE (("custom_attribute_id" = '12' AND "value_string" = 'Female') OR ("custom_attribute_id" = '12' AND "value_string" = 'Male'))
AND (("custom_attribute_id" = '17' AND "value_string" = 'Widowed') OR
("custom_attribute_id" = '17' AND "value_string" = 'Divorced') OR ("custom_attribute_id" = '17' AND "value_string" = 'Never married') OR
("custom_attribute_id" = '17' AND "value_string" = 'Married') OR ("custom_attribute_id" = '17' AND "value_string" = 'Separated'))

enter image description here

Second Query, which returns the result in, which I am interested but taking too much time

SELECT custom_attribute_values.attributable_id FROM custom_attribute_values
WHERE (("custom_attribute_id" = '12' AND "value_string" = 'Female') OR ("custom_attribute_id" = '12' AND "value_string" = 'Male'))
INTERSECT
SELECT custom_attribute_values.attributable_id FROM custom_attribute_values WHERE (("custom_attribute_id" = '17' AND "value_string" = 'Widowed') OR
("custom_attribute_id" = '17' AND "value_string" = 'Divorced') OR ("custom_attribute_id" = '17' AND "value_string" = 'Never married') OR
("custom_attribute_id" = '17' AND "value_string" = 'Married') OR ("custom_attribute_id" = '17' AND "value_string" = 'Separated'))

enter image description here

Upvotes: 1

Views: 1170

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1270883

You have conflicting conditions on each row. I'm pretty sure you want aggregation:

SELECT cav.attributable_id as Member_id
FROM custom_attribute_values cav
WHERE ("custom_attribute_id" = '12' AND "value_string" IN ('Female', 'Male')) OR 
      ("custom_attribute_id" = '17' AND "value_string" IN ('Widowed', 'Divorced', 'Never married', 'Married', 'Separated')
      )
GROUP BY cav.attributable_id
HAVING COUNT(DISTINCT "custom_attribute_id") = 2;  -- both match

The WHERE checks that either condition match. The HAVING validates that both match for a given member.

Note that you should avoid double quotes for identifiers. They just make queries harder to write and to read.

Upvotes: 2

Related Questions