Amazon alike collaborative filtering

[sumerianx sumerianx]

Hi, i am trying to create a very basic version of
"people who bought this also bought that".

so i have a table1 and table2, they look like this
table1 is all the products with details.
table2 is who bought what. the product_id in table2 is
the table1's id for the product.

table1
id | name
1 | apple
2 | orange
3 | banana
4 | milk
5 | chocolate

table2
user | product_id
john | 4
mike | 3
mike | 1
john | 2
john | 5

so lets say i wanna know " what other products were purchased by those who purchased milk ".

what do i do. i need to print out the names of the products excluding the milk itself.

i imagine the query has to connect table1 and table2 then find all the users who purchased milk in table2 and then find all the products purchased by those users and then connect those products with table1 to get their names.

can all this be done in just 1 MySQL query?

Thanks.



Edited 2 time(s). Last edit at 11/09/2007 09:49PM by sumerianx sumerianx.