September 30, 2004 at 4:34 pm
Hi,
I am developing an ecommerce store, almost finished but can't think how to achieve this scenerio:
"People who bought the items in your shopping basket also bought..."
Now, Ive had a go at this - but can't seem to get it to produce the right results.
DB structure
Products table
prod_id (key)
prod_name
...
OrderDetails Table
orderDetail_id (key)
order_id
prod_id
....
What I was aiming for is:
Look in the OrderDetails table, grouping by each order made (order_id) - to see if any of the orders have any of the products listed within the cart. And to return a list of products without the same products listed within the cart.
Can anyone help me here?
Thanks
John
October 1, 2004 at 7:14 am
Not tested this out on the tables, but think it will work.
select p.prod_name, o.prod_id from OrderDetails o, Products p
where o.order_id in (
select order_id from OrderDetails
where prod_id = @prodid)
and o.prod_id = p.prod_id
and o.prod_id <> @prodid
Replace @prodid with the product id the customer selects.
This will return the product name and the product id in case you require it later on in your code.
You can even enhance this by using 'top 10' outside this query to pick the most popular of the products. See BOL for info on how to do that. It's fairly simple.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply