Select question

[shawjames]

Hello,
I am trying to figure out how to do this, and I cannot get the syntax down. I have a shopping cart, and I want to show other products other customers have purchased. All items that have been purchased are in a table called carts.

The carts field has part number and order number. So given one part number, I want to find all other parts that were purchased from the same order number.

So the fields are carts.pnum and carts.onum.

Any clever ways to do this?

using:
mysql Ver 12.22 Distrib 4.0.16

[crislewis]

You could keep it simple by running two queries. First find a list of orders that have the part. Then use the list to query the orders. I would put a limit on the first so that you are not trawling the whole database.

In the long run the way many larger on-online shops do this is to maintain a top three assocations with each product. This is updated either as each cart is completed or as a batch update.