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?

Wouldn't it be possible to do `HAVING COUNT(DISTINCT attributes.value) > 2` — Thaillie, Feb 07 '16 at 23:06
I already have tried it (` >=2 `) but the result is the same as actual. So, you think it's totally impossible (the query can deeply change, obviously)? — fusion3k, Feb 07 '16 at 23:10
@Strawberry `id` for `posts`, `attributes` doesn't have primary key — fusion3k, Feb 08 '16 at 00:17
That is problematic. Relational data should always have a PRIMARY KEY. From MySQL's perspective, these colours are indistinguishable. — Strawberry, Feb 08 '16 at 00:18
@Strawberry This is the table I have, and I can't modify it :) In fact they have not problems with relationships (it exists only in relationships) and records are distinguishable because id+attr+value are uniques. — fusion3k, Feb 08 '16 at 01:23

score 2 · Accepted Answer · answered Feb 07 '16 at 23:32

  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

GROUP_CONCAT: search for subset and retrieve all values

1 Answers1