SQL for Product recommendation

In a shopping cart we can use the following logic to propose recommended products for a certain customer Customer 1.

In SQL, this is a scenario of recursive join.

We have

Customer Table (Entity)
Product Table (Entity)

and Customer_Product_Mapping (nicknamed cpm) (Relationship)

To derive a recommendation list we need to work on the relationship cpm, a ternary join (self join or recursive join)

1. We shall find the product list of our target customer : Customer 1 (cpm)

2. [cpm2] We shall then find the fellow customers who purchased at least 1 of the product Customer 1 has bought.

3. [cpm3] We shall find the rest of product list Customer 1 has not yet bought.

4. By joining the cpm2 and cpm3 we shall get the products peer/fellow customers has bought but not Customer 1, this the recommendation list for customer 1.

The SQL statement is:

SELECT cpm3.product_id,
cpm3.customer_id FROM Customer_Product_Mapping as cpm, Customer_Product_Mapping as cpm2,
Customer_Product_Mapping as cpm3
WHERE

cpm.customer_id = 1  -- produces the cpm

and cpm.product_id = cpm2.product_id and cpm2.customer_id <> 1  -- produces cpm2


and cpm3.product_id not in
(select distinct product_id FROM Table1 cpm WHERE cpm.customer_id = 1)  --produces cpm3

and cpm3.customer_id = cpm2.customer_id -- finally derives recommendation list