This called a relational division, and you can Google it. You want one
with a remainder for doing "shopping baskets".
Having said this, you are better off looking for a data mining tool
that will have the ability to set up the baskets on the fly for
somethign like Amazon.
Assuming you only want to have pairs in the baskets and do this only
once and not on the fly, you can set up a sparse matrix in SQL:
CREATE TABLE Pairs
(item_1_upc CHAR(13) NOT NULL,
item_2_upc CHAR(13) NOT NULL,
tally INTEGER DEFAULT 0 NOT NULL,
CHECK (item_1_upc < item_2_upc) ,
PRIMARY KEY (item_1_upc, item_2_upc));
Now go thru every order, pull out all possible pairs that occur more
than some threshold:
INSERT INTO Pairs(item_1_upc, item_2_upc, tally)
SELECT O1.upc, O2.upc, COUNT(*)
FROM Orders AS O1, Orders AS O2
WHERE O1.order_nbr = O2.order_nbr
AND O1.upc < O2.upc
GROUP BY O1.upc, O2.upc
HAVING COUNT(*) > @some_limit;
From the Pairs matrix, you can then build Triplets with whatever rules
you wish based on the tally. For example, given (a, b, 123) and
(b,c, 100) you know that (a,b,c, ??) cannot have more than 100
occurences, but if I have 1000 b's, the number could be as low as zero.
