Help with query to extract next best product

[Alan]

I'm having a bit of difficulty getting the results I need from our database.

In a nutshell I'm trying to work out trends in what people buy next. So, for
example, I'm trying to run a query that extracts all orders containing
product1 and then trying to run another query that gives me a count on
products purchased AFTER that product was purchased.

My table relationships are fairy standard for an order processing database:

Customer table (CustomerID) ---> Orders table (OrderID,OrderDate) -->
OrderDetails table (OrderID/ProductID) ---> Products table (ProductID)

I can easily extract buyers of product1 but I'm having difficulty retrieving
the first instance of product1 where product1 has been bought by the same
customer more than once. I've tried grouping by "First" in a "group by"
query but that doesn't give me the first record, it still gives me all of
them. Any pointers or ideas as to how my query should look?

Thanks

Alan

[Tom van Stiphout]

On Thu, 15 Dec 2005 12:49:54 +0000 (UTC), "Alan" <no****@nospam.com>
wrote:

I'm not thrilled with First and Last; would use Min and Max instead.
They have a more solid SQL underpinning.

Using the Northwind sample database:
List of customers with the first time they ordered productid=1:
SELECT Orders.CustomerID, Min(Orders.OrderDate) AS MinOfOrderDate
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
WHERE ((([Order Details].ProductID)=1))
GROUP BY Orders.CustomerID;
Save this as query Q1

Here is Q2: the list of subsequent orders
SELECT Orders.CustomerID, Orders.OrderDate
FROM Q1 INNER JOIN Orders ON Q1.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate)>[MinOfOrderDate]));

Q3: Minimum subsequent order:
SELECT Q2.CustomerID, Min(Q2.OrderDate) AS MinOfOrderDate
FROM Q2
GROUP BY Q2.CustomerID;

Q4: The products purchased in the subsequent order:
SELECT Orders.CustomerID, Products.ProductID, Products.ProductName
FROM Q3 INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order
Details] ON Orders.OrderID = [Order Details].OrderID) ON
Products.ProductID = [Order Details].ProductID) ON (Q3.MinOfOrderDate
= Orders.OrderDate) AND (Q3.CustomerID = Orders.CustomerID);

-Tom.

I'm having a bit of difficulty getting the results I need from our database.

In a nutshell I'm trying to work out trends in what people buy next. So, for
example, I'm trying to run a query that extracts all orders containing
product1 and then trying to run another query that gives me a count on
products purchased AFTER that product was purchased.

My table relationships are fairy standard for an order processing database:

Customer table (CustomerID) ---> Orders table (OrderID,OrderDate) -->
OrderDetails table (OrderID/ProductID) ---> Products table (ProductID)

I can easily extract buyers of product1 but I'm having difficulty retrieving
the first instance of product1 where product1 has been bought by the same
customer more than once. I've tried grouping by "First" in a "group by"
query but that doesn't give me the first record, it still gives me all of
them. Any pointers or ideas as to how my query should look?

Thanks

Alan

[Alan]

Thanks Tom, that gives me exactly what I want! If I turn the last query into
a make table and then run a group by query to count the product names, I can
see what the most popular "next" purchase is.

Also, if I drop Q3 and perform Q4 on Q2 I can find out in general what
people order (not just immediately after) after they've purchased said
product....unless my logic is flawed there?!

Thanks again, this has stopped me going bald with pulling my hair out!

Allan
"Tom van Stiphout" <no*************@cox.net> wrote in message
news:ep********************************@4ax.com...

On Thu, 15 Dec 2005 12:49:54 +0000 (UTC), "Alan" <no****@nospam.com>
wrote:

I'm not thrilled with First and Last; would use Min and Max instead.
They have a more solid SQL underpinning.

Using the Northwind sample database:
List of customers with the first time they ordered productid=1:
SELECT Orders.CustomerID, Min(Orders.OrderDate) AS MinOfOrderDate
FROM Orders INNER JOIN [Order Details] ON Orders.OrderID = [Order
Details].OrderID
WHERE ((([Order Details].ProductID)=1))
GROUP BY Orders.CustomerID;
Save this as query Q1

Here is Q2: the list of subsequent orders
SELECT Orders.CustomerID, Orders.OrderDate
FROM Q1 INNER JOIN Orders ON Q1.CustomerID = Orders.CustomerID
WHERE (((Orders.OrderDate)>[MinOfOrderDate]));

Q3: Minimum subsequent order:
SELECT Q2.CustomerID, Min(Q2.OrderDate) AS MinOfOrderDate
FROM Q2
GROUP BY Q2.CustomerID;

Q4: The products purchased in the subsequent order:
SELECT Orders.CustomerID, Products.ProductID, Products.ProductName
FROM Q3 INNER JOIN (Products INNER JOIN (Orders INNER JOIN [Order
Details] ON Orders.OrderID = [Order Details].OrderID) ON
Products.ProductID = [Order Details].ProductID) ON (Q3.MinOfOrderDate
= Orders.OrderDate) AND (Q3.CustomerID = Orders.CustomerID);

-Tom.

I'm having a bit of difficulty getting the results I need from our database.
In a nutshell I'm trying to work out trends in what people buy next. So, forexample, I'm trying to run a query that extracts all orders containing
product1 and then trying to run another query that gives me a count on
products purchased AFTER that product was purchased.

My table relationships are fairy standard for an order processing database:
Customer table (CustomerID) ---> Orders table (OrderID,OrderDate) -->
OrderDetails table (OrderID/ProductID) ---> Products table (ProductID)

I can easily extract buyers of product1 but I'm having difficulty retrievingthe first instance of product1 where product1 has been bought by the same
customer more than once. I've tried grouping by "First" in a "group by"
query but that doesn't give me the first record, it still gives me all of
them. Any pointers or ideas as to how my query should look?

Thanks

Alan