CODEWITHSUNDEEP
sqlite
Andreas B
Andreas B

Reputation: 107

SQLite query returns 0 results

I am having trouble with a query.

Fiddle: https://www.db-fiddle.com/f/JXQHw1VzF7vAowNLFrxv5/1

This is not going to work. So my question is: What has to be done to get a result when I wanna use both conditions.

(attr_key = 0 AND attr_value & 201326592 = 201326592)

AND

(attr_key = 30 AND attr_value & 8 = 8)

Thanks in advance!

Best regards

Upvotes: 0

Views: 51

Answers (1)

Tim Biegeleisen
Tim Biegeleisen

Reputation: 520908

One way to check for the presence of some number of key value pairs in the items_attributes table would be to use conditional aggregation:

SELECT i.id
FROM items i
LEFT JOIN items_attributes ia
    ON i.id = ia.owner
GROUP BY
    i.id
HAVING
    SUM(CASE WHEN ia.key = 0  AND ia.value = 201326592 THEN 1 ELSE 0 END) > 0 AND
    SUM(CASE WHEN ia.key = 30 AND ia.value = 8 THEN 1 ELSE 0 END) > 0

The trick in the above query is that we scan each cluster of key/value pairs for each item, and then check whether the pairs you expect are present.

Note: My query just returns id values from items matching all key value pairs. If you want to bring in other columns from either of the two tables, you may simply add on more joins to what I wrote above.

Upvotes: 1

Related Questions