从表中生成'客户也买了'

Question

I've got a 'cart' table as follows:

+----+---------+-----------+----------+----------+
| id | orderID | productID | quantity |  status  |
+----+---------+-----------+----------+----------+
|  1 |    3443 |       333 |        2 | complete |
|  2 |    3443 |       202 |        4 | complete |
+----+---------+-----------+----------+----------+

And so foreach order, a new row gets added for each item in the cart.

So I want to get an 'customers also bought' from this table. I'm using MySQL.

How can I do this? This is what I came up with at first:

SELECT * FROM `cart` WHERE `status` = 'complete' AND `productID` = '333' ORDER BY `ts` DESC

But of course this only shows me the the rows with that productID in. What I really want is the other products of orders that contain that productID.

How can I go about doing this?

Answer 1

I'd probably do this with a self-join on the cart table:

Select Distinct mapped.productID
  From cart main
  Join cart mapped On main.orderID = mapped.orderID
 Where main.productID != mapped.productID
   And main.productID = 333