Favorites Basket

Hi Developers,
I have created an Inventory system in the company I am working for, now the next they want me to make is to create something called the Favorite Basket, in which when I am entering a new Order say for example my order goes like this

1- Desktop PC
2- LCD Screen

They want me to create an Analysis from previous orders containing my order items and display ( Employees who Ordered Items 1 & 2 ) have also ordered the following : say for example in this case

1- Mouse :P
2- Pen Drive

Anyway my question is that how can I create this anaysis correctly is this an algorithim for it , please if anyone have done it before or have read about it. inform me.

I wish we can charge them :P

Thanks
Aiman Al-Jumoay

seems simple enough, just look at past orders for other people who have bought it and find the most bought item from those people

yeah but how can i know what is the most item ordered with it.

I mean the concept is clear but programmaticly what is the Algorithim for it ??
should I start counting for each item ordered with this item ?

if anyone have done it before or have read of how to do it, please help ot give links

Thanks
Aiman Al-Jumoay

Evidently there's two stages to this -- find the users who have previously ordered those same items, and find the items most commonly ordered by those users other than those items.

I expect that inserting the ordered items into a temporary table would be helpful.

You could find the users with code similar to ...

Code:

---

Select username
from  orders
where  ordered_item in
      (
      select ordered_item
      from  temp_new_order
      )
group by username
having count(distinct ordered_item) =
      (select count(*) from temp_new_order)

---

... and then find the most popular ordered items for those users with something like ...

Code:

---

Select ordered_item
from
(
Select ordered_item, sum(ordered_qty) sum_qty
From  orders
Where  username in (previous query inserted here)
and    ordered_item not in
      (select ordered_item from temp_new_order)
group by ordered_item
order by 2 desc
)
Where rownum <= 5

---

This may not be the best performing code, but at least it's a workable method to start with.

I would be tempted to go with something along the lines of:

Code:

---

SELECT ordered_item
FROM (SELECT ordered_item, count(*)
      FROM orders
      WHERE order_no IN (SELECT order_no
                        FROM orders
                        WHERE ordered_item IN (list_from_current_order) and
            ordered_item NOT IN (list_from_current_order)
      GROUP BY ordered_item
      ORDER BY count(*) DESC)
WHERE rownum < however_many_you_want_to_show

---

to generate the list

John