Re: Amazon alike collaborative filtering

[pravin shukla]

sumerianx sumerianx Wrote:
-------------------------------------------------------
> 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.



Hello sumerianx,

I have used some inner join techniques to get the required records.


this is my record table

person_name id
pravin 1
navin 2
sushma 3
sandip 1
pravin 3
navin 4



this is my products table

id name
1 milk
2 orange
3 apple
4 pineapple

select R.person_name,F.id,P.name from records R inner join (select id from products) as K on K.id=R.id and R.id=1
left join (select id,person_name from records) as F on F.person_name=R.person_name left join (select id,name from products) as P on P.id=F.id

The first part query will you all the names of the person having milk .

The secong part will give all the product ids having different products who bought milk

The third part will give the all product names

I hope it will hlp you



Edited 1 time(s). Last edit at 12/25/2007 09:53PM by pravin shukla.