# Compare two sets with a quantity field

Imagine I have a basket from an online shop, with id of item and the quantity. I have a list of items of quantity and I would like to find a basket with the exact same items and quantity for each.

I have found solutions to find matching sets, but not with duplicates or quantity field.

The basket could contain multiple times the same item id, or I could also choose to have a quantity field, depending on what's better

thanks

two solutions I see for database format :

``````id_basket id_product quantity
1           1           2
1           2           1
2           1           2
2           2           1
3           1           1
3           2           1
``````

or

``````id_basket id_product
1           1
1           1
1           2
2           1
2           1
2           2
3           1
3           2
``````

basket 1 and 2 contain the same thing, but not the third, and I would like to be able to find a basket that contains a list of product using "raw" data, so the idea is not to find duplicates on the database, but find one that matches a static list (like find a basket that contain 2x item 1 and 1x item 2)

• Show the solution you've found and what is exactly the problem with adding quantity to comparing sets.
– Serg
Jun 2, 2016 at 14:59
• dba.stackexchange.com/questions/45512/… "there is a UNIQUE constraint on (wordid, docid)" Jun 2, 2016 at 15:01
• In MySql fist have baskets totals and basket-item subtotals . Then do triangle self join of baskets by equal totals and intesect basket's items subtotals. MySql has no intersect directly, use full join on <=> item_id, item_quantity.
– Serg
Jun 2, 2016 at 17:37
• so like one join per item ? Jun 2, 2016 at 17:50
• Please provide sample data in Mysql consumable format to show it in action.
– Serg
Jun 2, 2016 at 17:57

• no I don't need to use this 10s of times per second, if noone gives a better idea I will use this solution. Here is something based on your solution that works EXPLAIN SELECT *, GROUP_CONCAT(id_product) AS thegroup FROM `basket_item` GROUP BY id_basket HAVING thegroup = '1,1,2' However I think that once the table will be huge, that would take a lot of time ... Jun 3, 2016 at 12:00