Post reply

Ecomm. query help please!

  • September 30, 2004 at 4:34 pm

    #60112

    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

    #525014

    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.

    ------------------------------
    The Users are always right - when I'm not wrong!

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply