Using IN with LIMIT in MySql Here is my old query which was working fine :

SELECT tag.tag 
FROM tag, v_bookmark_tag_assoc 
WHERE tag.tag_id = v_bookmark_tag_assoc.tag_id 
AND v_bookmark_tag_assoc.bookmark_id = '123' 
ORDER BY RAND() LIMIT 10


Now I need to update the query to feed in all the "v_bookmark_tag_assoc.bookmark_id" as in IN clause of MySql, so that I can have all the result up in one output array for all input ids.

Problem is if if modify query like so :

SELECT tag.tag 
FROM tag, v_bookmark_tag_assoc 
WHERE tag.tag_id = v_bookmark_tag_assoc.tag_id 
AND v_bookmark_tag_assoc.bookmark_id IN(123,456,678,890) 
ORDER BY RAND() LIMIT 10


It doesn't make sense at all using LIMIT of 10 for each input id is illegitimate here this way.

Please help me out to modify this query in such a way that I can have 10 tags for each input bookmark id.
There are several different ways to approach to problem.  One is simply to group by the bookmarks you care about then obtain a list of tags for each - the substring_index function is being used to limit the number of tags to ten which are in ascending order.  Order and the separation character can be changed to fit your purpose.

select bm.bookmark_id
     , substring_index(group_concat(tag.tag order by tag.tag asc separator ','),',',10) as tag_list
  from v_bookmark_tag_assoc bm
     , tag
 where bm.bookmark_id in (123,456,678,890)
   and tag.tag_id=bm.tag_id
 group by bm.bookmark_id