GROUP_CONCAT: search for subset and retrieve all values

Question

I have this two tables:

posts

+--------------------------------------------------------------------+
|id          ¦status¦type ¦url                                       |
+------------+------+-----+------------------------------------------+
|25949664    ¦80    ¦link ¦http://example.com/25949664               |
|25777570    ¦80    ¦photo¦http://example.com/25777570               |
+--------------------------------------------------------------------+

attributes

╔════════════╦════════════╦══════════════════════════════════════════╗
║id          ║attr        ║value                                     ║
╠════════════╬════════════╬══════════════════════════════════════════╣
║25949664    ║timestamp   ║1430836105                                ║
║25949664    ║tag         ║red                                       ║
║25949664    ║tag         ║yellow                                    ║
║25949664    ║tag         ║brown                                     ║
║25949664    ║source      ║http://example.com/wallin/                ║
║25949664    ║source_title║wallin                                    ║
║25949664    ║state       ║published                                 ║
║25949664    ║format      ║html                                      ║
║25777570    ║timestamp   ║1430836105                                ║
║25777570    ║tag         ║red                                       ║
║25777570    ║tag         ║yellow                                    ║
║25777570    ║tag         ║brown                                     ║
║25777570    ║tag         ║black                                     ║
║25777570    ║tag         ║orange                                    ║
╚════════════╩════════════╩══════════════════════════════════════════╝

Performing this query:

  SELECT posts.id, GROUP_CONCAT(attributes.value) as tags
    FROM posts
    JOIN attributes ON attributes.id = posts.id
   WHERE ( attributes.attr = 'tag' )
     AND ( attributes.value IN ('red','brown') )
GROUP BY posts.id
  HAVING COUNT(DISTINCT attributes.value) = 2

I have this result:

╔════════════╦════════════╗
║id          ║tags        ║
╠════════════╬════════════╣
║25949664    ║red,brown   ║
║25777570    ║red,brown   ║
╚════════════╩════════════╝

I would rather have this:

╔════════════╦════════════════════════════════╗
║id          ║tags                            ║
╠════════════╬════════════════════════════════╣
║25949664    ║red,yellow,brown                ║
║25777570    ║red,yellow,brown,black,orange   ║
╚════════════╩════════════════════════════════╝

Practically, having n tags, I would retrieve all post's tags performing only one query.

Anyone have a suggestion, or this is totally impossible?

Answer 1

  SELECT posts.id, GROUP_CONCAT(attributes.value) as tags
    FROM posts
    JOIN attributes ON attributes.id = posts.id
   WHERE ( attributes.attr = 'tag' )
     AND ( attributes.value IN ('red','brown') )
GROUP BY posts.id

With the and condition, you only get the attributes red and brown. So you need to remove this and condition and rewrite that condition.

I think that this can help you:

 SELECT posts.id, GROUP_CONCAT(attributes.value) as tags
    FROM posts
    JOIN attributes ON attributes.id = posts.id
   WHERE ( attributes.attr = 'tag' )     
GROUP BY posts.id
  HAVING Find_In_Set('red',tags)>0 AND Find_In_Set('brown',tags)>0

Online Demo