Sql: choose all baskets containing a set of particular items

Question

Eddy has baskets with items. Each item can belong to arbitrary number of baskets or can belong to none of them.

Sql schema to represent it is as following:

tbl_basket
- basketId

tbl_item
- itemId

tbl_basket_item
- pkId
- basketId
- itemId

Question: how to select all baskets containing a particular set of items?

UPDATE. Baskets with all the items are needed. Otherwise it would have been easy task to solve.

UPDATE B. Have implemented following solution, including SQL generation in PHP:

SELECT basketId
FROM   tbl_basket
JOIN   (SELECT basketId FROM tbl_basket_item WHERE itemId = 1  ) AS t0 USING(basketId)
JOIN   (SELECT basketId FROM tbl_basket_item WHERE itemId = 15 ) AS t1 USING(basketId)
JOIN   (SELECT basketId FROM tbl_basket_item WHERE itemId = 488) AS t2 USING(basketId)

where number of JOINs equals to number of items.

That works good unless some of the items are included in almost every basket. Then performance drops dramatically.

UPDATE B+. To resolve performance issues heuristic is applied. First you select frequency of each item. If it exceeds some threshold, you don't include it in JOINs and either:

apply post-filtering in PHP
or just don't apply filter by particular itemId, giving a user approximate results in a resonable amount of time

UPDATE B++. Seems that current problem have no nice solution in MySQL. This point raises one question and one solution:

(question) Does PostgreSQL have some advanced indexing techniques which allows to solve this problem without doing a full scan?
(solution) Seems that it could be solved nicely in Redis using sets and SINTER command to get an intersection.

How about `SELECT (DISTINCT if needed) tb.* FROM tbl_basket_item tbi JOIN tbl_basket tb ON tbi.basketId=tb.basketId JOIN tbl_item ti ON tbi.itemID = ti.itemId WHERE tbi.itemId IN (1,2,3, ...)`? — Lukasz Szozda, Sep 04 '15 at 06:59
Using IN would also return baskets that only contain a part of the needed items. — Mihai Ovidiu Drăgoi, Sep 04 '15 at 07:05
I think this is more of a "how would you do this in a DBMS of you choice" kind of question :) — Mihai Ovidiu Drăgoi, Sep 04 '15 at 07:15
@jarlh Not at all) Just added some story behind not to bore SO people with business stuff. — Denis Kulagin, Sep 04 '15 at 08:18
Looks like a relational division problem. Standard solution is the double NOT EXISTS(). — joop, Sep 07 '15 at 09:23
Has been answered more than once. Please look at the other answers. [ I added the tag, so they should show up in the `related` section to the right -->> ] For instance: http://stackoverflow.com/questions/32228433/postgres-exclusive-tag-search — joop, Sep 07 '15 at 09:31

score 1 · Answer 1 · answered Sep 04 '15 at 07:03

1

I think the best way is to create a temporary table with the set of needed items (procedure that takes the item ids as parameters or something along those lines) and then left join it with all of the above tables joined together.

If for a given basketid you have NO nulls on the right side of the left join, the basket contains all the needed items.

answered Sep 04 '15 at 07:03

Mihai Ovidiu Drăgoi

1,307
1
10
16

PS - if you want the basket to contain ONLY those items, do a full join and make sure there are no nulls on either side for a given basketid – Mihai Ovidiu Drăgoi Sep 04 '15 at 07:09
That counts as a solution for sure. Just hoped to find something simpler and more like a ready-recipe for the situation looks like a generic task to me. – Denis Kulagin Sep 04 '15 at 08:23
Only other solution i can think of implies string concatenation - the performance drop would probably be too high. – Mihai Ovidiu Drăgoi Sep 04 '15 at 08:25

score 1 · Answer 2 · answered Sep 07 '15 at 10:03

-- the table definitions
CREATE TABLE basket ( basketid INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE item ( itemid INTEGER NOT NULL PRIMARY KEY);
CREATE TABLE basket_item
        ( basketid INTEGER NOT NULL REFERENCES basket (basketid)
        , itemid INTEGER NOT NULL REFERENCES item (itemid)
        , PRIMARY KEY (basketid, itemid)
        );

-- the query
SELECT * FROM basket b
WHERE NOT EXISTS (
        SELECT * FROM item i
        WHERE i.itemid IN (1,15,488)
        AND NOT EXISTS (
                SELECT * FROM basket_item bi
                WHERE bi.basketid = b.basketid
                AND bi.itemid = i.itemid
                )
        );

score 0 · Answer 3 · answered Sep 04 '15 at 07:03

0

If you are going to provide the list of items, then edit id1, id2, etc. in below query:

select distinct t.basketId
from tbl_basket_item as t
where t.itemID in (id1, id2)

will give all baskets containing a set of items. No need to join any other tables as your requirements don't need them.

answered Sep 04 '15 at 07:03

Emacs User

1,457
1
12
19

1

Using IN would also return baskets that only contain a part of the needed items. I don't think that's what the OP asked for. – Mihai Ovidiu Drăgoi Sep 04 '15 at 07:06
There is no separate schema linking items with baskets, which your interpretation of the question entails. I'm sure OP would have shown that. In any case, I can revise if that's what OP wants. – Emacs User Sep 04 '15 at 07:10

score 0 · Answer 4 · answered Sep 07 '15 at 11:00

0

The simplest solution is to use HAVING clause.

SELECT basketId
FROM   tbl_basket
WHERE itemId IN (1,15,488)
HAVING Count(DISTINCT itemId) = 3 --DISTINCT in case we have duplicate items in a basket
GROUP BY basketId

answered Sep 07 '15 at 11:00

Jakub Kania

15,665
2
37
47

I'll try that, but not sure it performs well for heavy tables. – Denis Kulagin Sep 07 '15 at 11:03
@DenisKulagin That would depend on your data but certainly it should behave better then the query in your question. Well, at least in most cases. – Jakub Kania Sep 07 '15 at 11:43

Sql: choose all baskets containing a set of particular items

4 Answers4

Linked