How to get all possible combinations from a tag and item table?

Question

I have a table like below

============
item | tag
-----+------
111  | amaretto
-----+------
111  | blueberry
-----+------
222  | amaretto
-----+------
333  | blueberry
-----+------
333  | chocolate
-----+------
444  | chocolate
-----+------
444  | amaretto
-----+------
555  | chocolate
-----+------
666  | blueberry
-----+------
666  | amaretto
-----+------
666  | chocolate
============

The columns item and tag together constitutes a composite primary key. Lets imagine like the the item as an ice cream and the tags as the available flavors. For example, if I search for amaretto (in the case of ice cream), I want to display all possible combinations in which amaretto ice cream is available, like amaretto, amaretto+chocolate, blueberry+amaretto etc. If I search for tag amaretto, I want to display all possible combinations like;

amaretto (222)
amaretto,blueberry (111)
amaretto,chocloate (444)
amaretto,blueberry,chocolate (666)

And the combinations amaretto,blueberry and blueberry,amaretto are same.

How can I do this? Do i need additional tables? I use PHP+MySQL.

What SQL can do is retrieve all the flavours of a given ice cream, by querying the primary key. Once you collected all the flavours, enumerating all the possible combinations is up to you. — kuroi neko, Nov 24 '15 at 09:56
if you know probability than i think it have n number of probability of combination of this — NullPoiиteя, Nov 24 '15 at 09:59
possible duplicate of http://stackoverflow.com/questions/26998962/mysql-get-all-possible-combinations-of-certain-rows — Calimero, Nov 24 '15 at 10:05

Mihai · Answer 1 · 2015-11-24T10:50:52.353

1

SELECT tt.item,
GROUP_CONCAT(DISTINCT  tt.tag) FROM mytable tt
 JOIN mytable ttt 
ON tt.item=ttt.item
GROUP BY tt.item
HAVING SUM(tt.tag='yourtag')>0

One solution is to GROUP BY the results of GROUP_CONCAT if they are ordered first

SELECT MIN(item),tags FROM
(SELECT  tt.item,
GROUP_CONCAT(DISTINCT  tt.tag ORDER BY tt.tag) tags FROM mytable tt
 JOIN mytable ttt 
ON tt.item=ttt.item
GROUP BY tt.item
HAVING SUM(tt.tag='amaretto')>0)x
GROUP BY tags

edited Nov 24 '15 at 10:50

answered Nov 24 '15 at 10:09

Mihai

26,325
7
66
81

what are `t`, `tt` and `ttt`? – Alfred Nov 24 '15 at 10:14
@blasteralfredΨ Highly imagniative aliases – Mihai Nov 24 '15 at 10:14
@Mihai, if my table name is like mytable, where I have to provide my table name `mytable`? – Alfred Nov 24 '15 at 10:15
@blasteralfredΨ sqlfiddle is down,this is tested on my machine – Mihai Nov 24 '15 at 10:18
@blasteralfredΨ This is the demo with your inital data http://www.sqlfiddle.com/#!9/4e1611/5 – Mihai Nov 24 '15 at 10:33
I have added new entries to my table which can be found here: http://freetexthost.com/40ow4gkf5c and when I run the query, I get duplicate results, `444 amaretto,chocolate` and `888 chocolate,amaretto` which are same (irrespective of 444 & 888). Here is a screenshot https://i.stack.imgur.com/xShne.jpg . I want to elimitate duplicates. How can I do this? – Alfred Nov 24 '15 at 10:45
The flavor combinations of items 444 and 888 as well as 666 and 999 are same irrespective of their order of appearance. So instead of 7 results in total, I need only 5. http://i.stack.imgur.com/4S13O.jpg – Alfred Nov 24 '15 at 10:53

Edper · Answer 2 · 2015-11-24T10:32:25.957

0

You probably want GROUP_CONCAT and then using LIKE statement.

SELECT * FROM (
SELECT item, GROUP_CONCAT(DISTINCT tag) as TagCombinations
FROM tbl
GROUP BY item ) A
WHERE TagCombinations LIKE 'amaretto%'

edited Nov 24 '15 at 10:32

answered Nov 24 '15 at 10:09

Edper

9,144
1
27
46

1

Since you are using pre aggregate filtering you are removing all the non amaretto rows – Mihai Nov 24 '15 at 10:16

Strawberry · Accepted Answer · 2015-11-24T11:09:14.120

SELECT GROUP_CONCAT(DISTINCT a.tag) tags 
  FROM my_table a 
  JOIN my_table b 
    ON b.item = a.item 
 WHERE b.tag = 'amaretto' 
 GROUP 
    BY a.item;
+------------------------------+
| tags                         |
+------------------------------+
| amaretto,blueberry           |
| amaretto                     |
| amaretto,chocolate           |
| amaretto,blueberry,chocolate |
+------------------------------+
4 rows in set (0.00 sec)

To solve the OP's amended question, a rare instance of DISTINCT and GROUP BY in the same query together...:

SELECT DISTINCT GROUP_CONCAT(a.tag ORDER BY a.tag) tags 
  FROM mytable a 
  JOIN mytable b 
    ON b.item = a.item 
   AND b.tag <= a.tag 
 WHERE b.tag = 'amaretto' 
 GROUP 
    BY a.item;
+-------------------------------------+
| tags                                |
+-------------------------------------+
| amaretto,blueberry                  |
| amaretto                            |
| amaretto,chocolate                  |
| amaretto,blueberry,chocolate        |
| amaretto,blueberry,chocolate,pastry |
+-------------------------------------+
5 rows in set (0.01 sec)

Note that multiple identical combinations can be symptomatic of a lack of normalisation.

I have added new entries to my table which can be found here: http://freetexthost.com/40ow4gkf5c and when I run the query, I get duplicate results, `444 amaretto,chocolate` and `888 chocolate,amaretto` which are same (irrespective of 444 & 888). Here is a screenshot https://i.stack.imgur.com/xShne.jpg . I want to elimitate duplicates. How can I do this? — Alfred, Nov 24 '15 at 10:45

How to get all possible combinations from a tag and item table?

3 Answers3