I have a sqlite database with 2 tables containing customer information. One table (table1) store fixed information for the customer (e.g. customer_id, name, age, etc.). The other table (table2) stores properties as a dictionary (key-value pairs) with 3 columns - customer_id, property_key, property_value.
Now, I am trying to query a customer by matching all its properties.
SELECT customer_id FROM table1 WHERE name=<SOME-NAMES>
INTERSECT
SELECT customer_id FROM table2 WHERE property_key=<KEY-1> AND property_value=<VALUE-1>
INTERSECT
SELECT customer_id FROM table2 WHERE property_key=<KEY-2> AND property_value=<VALUE-2>...
I achieve this by using INTERSECT, which works properly until I encounter a rare entry which contains more than a thousand properties, (I will need a thousand INTERSECT) resulting in an error due to too many terms in compound SELECT.
I do not want to put new fixed columns in table1, since the property_keys are different in every customer. I only think of fetching all customer ids with 50 properties a time and process the fetched customer ids afterwards in another program.
Is there any solution to do this more efficiently?
Finding a customer with all matching properties can be done by counting the amount of total properties on that customer and comparing that number to the number of matching/filtered properties.
SELECT table1.customer_id
FROM table1
JOIN (
SELECT customer_id, COUNT(*) AS 'count'
FROM table2
GROUP BY customer_id
) properties ON properties.customer_id = table1.customer_id
JOIN (
SELECT customer_id, COUNT(*) AS 'count'
FROM table2
WHERE ( -- your filters here
property_key = 'key1' AND property_value = 'val1' OR
property_key = 'key2' AND property_value = 'val2' OR
property_key = 'key3' AND property_value = 'val3'
)
GROUP BY customer_id
) matches ON matches.customer_id = table1.customer_id
WHERE properties.count = matches.count
Edit: This query as it is cannot be used to find customers without properties.