1

I'm having a lot of problems with selecting data from a table containing a key/value pairs.

I have found a few questions similar to mine, but unfortunately I'm not even close to find a solution.

Need a MySQL query for selecting from a table storing key value pairs

How to select data from a key value pair table

Anyway, let suppose that this is my table:

user_id | item_id | item_amount
--------------------------------
 1      | 12      | 5
 1      | 15      | 10 
 2      | 12      | 20
 2      | 15      | 30
 3      | 12      | 1
 3      | 30      | 5

Now, I want to perform a search to SELECT users who are having between 5 and 50 of items of id 12 AND 15.

I was (very naively) trying something like

SELECT user_id 
FROM user_item 
WHERE (item_id = 12 AND item_amount BETWEEN 5 AND 50) 
AND (item_id = 15 AND item_amount BETWEEN 5 AND 50)

Of course, it doesn't work. Thank you for any feedback.

Sloan Thrasher
  • 4,953
  • 3
  • 22
  • 40
ecetera
  • 33
  • 2
  • 4

2 Answers2

3

You are close:

SELECT user_id
FROM user_item
WHERE (item_id = 12 AND item_amount BETWEEN 5 AND 50) OR
      (item_id = 15 AND item_amount BETWEEN 5 AND 50)
GROUP BY user_id
HAVING COUNT(DISTINCT item_id) = 2;

The WHERE clause filters to rows where either condition matches. The HAVING guarantees that both conditions match for a given user.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
0
SELECT user_id FROM user_item WHERE (item_id = 12 AND item_amount BETWEEN 5 AND 50) OR (item_id = 15 AND item_amount BETWEEN 5 AND 50)

How about this?

Chris Chen
  • 1,228
  • 9
  • 14