I am posting this because I know I will need this soon or later.

I have built a simple recommendation for one of the websites I work with. It is only based on social relationships and does not take into account other parameters. The basic idea is if I like an item, select other items liked by people who also like this item and it can be used to build lists of similar items as seen on Amazon or YouTube. If we use as an example people who like movies (tables Person, Movie and many-to-many relationship PersonMovie), this can be expressed in SQL using several joins:

1.  
2. SELECT DISTINCT Movie.*, COUNT(Person.ID) AS PersonCount
3. FROM Movie
4. JOIN PersonMovie ON Movie.ID = PersonMovie.MovieID
5. JOIN Person ON PersonMovie.PersonID = Person.ID
6. JOIN PersonMovie AS PersonMovie1 ON Person.ID = PersonMovie1.PersonID
7. JOIN Movie AS Movie1 ON PersonMovie1.MovieID = Movie1.ID
8. WHERE Movie1.ID = 1 AND Movie.ID != Movie1.ID
9. GROUP BY Movie.ID
10. ORDER BY PersonCount DESC
11.  

First we join movies to people and then backwards to the specific movie with ID = 1 that we want to get similar movies for. PersonCount is the relevance factor — the higher it is, the more people like the movie.