1

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.

1
  • Google "mysql union"...
    – tombom
    Mar 3, 2017 at 9:04

1 Answer 1

1

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
0

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.