CHAPTER 15 ?– PRODUCT RECOMMENDATIONS 463
We join two instances of order_detail??”which we??™ve named od1 and od2??”on their order_id
fields and filter on the product_id value in od1 for the product we??™re looking for. This way, in
the od2 side of the relationship, we will have all of the products that were ordered in orders
that also contain the product we??™re filtering for. To more easily understand this, take a look at
the diagram in Figure 15-3. In the diagram, we have two orders (1 and 2), each having three
products (1, 2, 3, and 2, 5, 7 respectively). Following the relationship between od1 and od2, you
can see that is quite easy to find what products were ordered with a particular product.
Figure 15-3. Using order details to find product recommendations
Let??™s see how we can implement this relationship with SQL code. The following query retrieves
all the products that were ordered together with the product identified by a product_id of 4:
SELECT od2.product_id
FROM order_detail od1
JOIN order_detail od2
ON od1.order_id = od2.order_id
WHERE od1.product_id = 4;
This code returns a long list of products, which includes the product with the product_id
of 4, such as this one:
product_id
----------
4
5
10
43
4
5
10
23
25
28
4
10
12
14
43
CHAPTER 15 ?– PRODUCT RECOMMENDATIONS 464
This result is good to start with, but it??™s less than perfect.
Pages:
559
560
561
562
563
564
565
566
567
568
569
570
571
572
573
574
575
576
577
578
579
580
581
582
583