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?