simple sql select over 3 tables

Question

I'm stuck with Mysql... I'm sure this question have been asked hundreds of times but without any keyword. I've 2 tables and a view:

tbl_user_measure

measure_cat   userid   amount
------------------------------
7             1        78
5             1        96
4             1        78

tbl_itemcat_measure

measure_cat   item_cat   
----------------------
7             1       
5             1        
4             1        

vw_allitems

measure_cat   min     max   item_cat   itemid
---------------------------------------------------
7             76       81      1           1
5             97       100     1           1
4             79       81      1           1
7             76       81      1           11
5             95       97.5    1           11
4             76       79      1           11
4             33       12      2           5

What I'm looking for is the item(s) whose min and max values match in all measure_cat's of the specific item_cat the users values (max > tbl_user_measure.amount > min).

My approach was

SELECT distinct
v.itemid,
v.measure_cat_id,
v.min,
v.max
FROM
    vw_allitems v, tbl_user_measure um, tbl_user u
where
    um.tbl_measure_category_id = v.measure_cat_id and
    (um.amount >= v.min and um.amount <= v.max) and
    v.productid = 1

This resultes in:

itemid  measure_cat_id  min max
---------------------------------------------------
1           7       76  81
7           7       76  81
10          7       76  81
11          7       76  81
11          4       76  79
11          5       95  9

What I need is just the itemid '11' because its values fit in all categories.

Answer 1

Looks like you're almost there. Your query returns all matching rows; change it so that it returns the itemid and the count, grouped by itemid. All that's left is just to return those items where the count is equal to the number of rows that user has in the first table.

UPDATE: This should do what you need:

SELECT x.itemid, x.measure_cat, x.min, x.max
FROM
 (SELECT vw.itemid i, u.userid u, count(*) mc
  FROM vw_allitems vw
  JOIN tbl_user_measure u ON vw.measure_cat=u.measure_cat
  WHERE u.amount>=vw.min and u.amount<=vw.max
  GROUP BY vw.itemid, u.userid) match_counts
JOIN
 (SELECT userid u, count(*) uc
  FROM tbl_user_measure
  GROUP BY userid) user_counts
ON match_counts.u = user_counts.u
 AND match_counts.mc=user_counts.uc
JOIN
 vw_allitems x
ON match_counts.i = x.itemid
WHERE match_counts.u=1

Quick walkthrough. The first subquery takes every combination of user and item, and counts the number of matches. The second subquery counts how many entries that user has in tbl_user_measure. The join selects just those users and items where the match count equals the total count for that user (i.e. all the user's entries match), then finally we join it to the original data to return the original rows from vw_allitems and select just the user we are interested in.

Answer 2

Try this query:

SELECT v.itemid,
v.measure_cat_id,
v.min,
v.max
FROM  vw_allitems as v,
INNER JOIN tbl_user_measure as um
ON um.measure_cat = v.measure_cat and um.amount >= v.min and um.amount <= v.max
INNER JOIN tbl_itemcat_measure as im
ON im.item_cat=um.item_cat