We have a pretty straightforward Order, Customer, Products table setup.
I'd like to figure out a query that would tell me which products tend
to get ordered together.

For example:

Create Table Orders (
Cust_ID INTEGER,
Order_ID INTEGER);

INSERT INTO Orders (Cust_ID, Order_ID)
VALUES
(333,1),
(555,2),
(777,3);

CREATE TABLE Items (
Item_ID INTEGER,
Order_ID INTEGER,
Item_Desc CHAR(50) ,
Qty INTEGER);


INSERT INTO Items (Item_ID,Order_ID, Item_Desc, Qty) VALUES
(900,1,'Widget A', 10),
(901,1,'Widget B', 12),
(902,1,'Widget G', 12),

(903,2,'Widget B', 2),
(904,2,'Widget D', 8),

(905,3,'Widget B', 5),
(906,3,'Widget A', 4),
(907,3,'Widget C', 22);

I'd like to somehow have it figure out that 'Widget A' gets ordered
with 'Widget B' X times and have it sort by frequency. I realize this
might be a long running process, but can live with that.

Eventually, I'd like it to tell me which products get ordered alot so I
can put them next to "related" products in the warehouse.

I can do some of the logic in the code; I'd appreciate it if someone
could at least get me started on the query.

On a side note, can SQL do recursion?

Thanks,

Lee

lhenkel (AT) gmail (DOT) com wrote:
Here's a first attempt (SQL-92). I've included my assumed structure of
your products table. You made this harder than it should be because
none of the tables you posted have any keys!

CREATE TABLE Products (Item_Desc CHAR(50) NOT NULL PRIMARY KEY);

SELECT Item_Desc1, Item_Desc2, COUNT(*) AS cnt
FROM
(SELECT W1.Item_Desc, W2.Item_Desc
FROM Products AS W1
JOIN Products AS W2
ON W1.Item_Desc < W2.Item_Desc
JOIN Items AS I
ON I.Item_Desc = W1.Item_Desc
OR I.Item_Desc = W2.Item_Desc
GROUP BY W1.Item_Desc, W2.Item_Desc, I.Order_ID
HAVING MIN(I.Item_Desc)<MAX(I.Item_Desc)) AS
T(Item_Desc1,Item_Desc2)
GROUP BY Item_Desc1, Item_Desc2 ;


Yes. If your version of SQL supports the WITH clause.

--
David Portas

lhenkel (AT) gmail (DOT) com wrote:


select i1.item_desc, i2.item_desc, count(*)
from items i1
join items i2
on i2.order_id=i1.order_id
and i2.item_desc>i1.item_desc
group by i1.item_desc, i2.item_desc
go

Tested in dbw luw v 8.1.9 with result:

ITEM_DESC ITEM_DESC 3
--------------- --------------- ----
Widget A Widget B 2
Widget A Widget C 1
Widget A Widget G 1
Widget B Widget C 1
Widget B Widget D 1
Widget B Widget G 1

David,

Thanks for the reply.. I wasn't able to get the query to run (I'm using
FoxPro and it's weird about subqueries.

Good to know about the recursion. I'll have to play with that when I
get a chance later.

Thanks again

Lee

Bob,

Thanks.. That seems to work the way I was expecting. Even runs
relatively fast on my live data.

PS Sorry my structure was a little weird; I'm working on someone elses
DB and some of it's weirdness crept into my example table.