CODEWITHSUNDEEP
mysqlsqlschema
Alfred
Alfred

Reputation: 21396

How to find similar tag cominations from single table?

I have a table like below

=====================
item|   flavor
====+================
111 |   amaretto
111 |   blueberry
222 |   amaretto
333 |   blueberry
333 |   chocolate
444 |   chocolate
444 |   amaretto
555 |   chocolate
666 |   blueberry
666 |   amaretto
666 |   chocolate
777 |   pastry
777 |   blueberry
777 |   amaretto
777 |   chocolate
888 |   amaretto
888 |   chocolate
999 |   chocolate
999 |   blueberry
999 |   amaretto
101 |   amaretto
101 |   blueberry
=====================

The column item indicates name of ice cream and the flavor column shows the flavor combination contained in it. I want my db to return me alternate ice creams in case if I dont have one required. For example, if I am looking item 111, I want 111 and 101, which has exact same flavor combination. I have a query like;

SELECT item
FROM `mytable`
GROUP BY item
HAVING SUM(tag = 'amaretto') > 0
   AND SUM(tag = 'blueberry ') > 0

But it returns a result like;

111
666
777
999
101

This is because all these records have amaretto and blueberry in them. But for 666, 777 and 999, there are additional flavors in them. I dont want to display them. Instead, I just want 111 ad 101. Is there any way to achieve this? Do I need additional tables / can I achieve this using my current schema? I use PHP + MySQL.

Thank you.

Upvotes: 1

Views: 35

Answers (2)

Thorsten Kettner
Thorsten Kettner

Reputation: 95101

With the aggregate function GROUP_CONCAT you get the flavor list per item. You can then look for duplicate lists and show the associated item list.

select flavors, group_concat(item order by item) as items
from
(
  select item, group_concat(tag order by tag) as flavors
  from mytable
  group by item
) items_with_flavorlist
group by flavors
having count(*) > 1;

Upvotes: 0

jarlh
jarlh

Reputation: 44805

SELECT item
FROM `mytable`
GROUP BY item
HAVING SUM(tag = 'amaretto') > 0
   AND SUM(tag = 'blueberry ') > 0
   and sum(tag not in ('amaretto','blueberry ')) = 0

I.e. add sum(tag not in ('amaretto','blueberry ')) = 0 to make sure no other flavors are included.

Upvotes: 1

Related Questions