Customers who bought this also bought this... Help

Hi trying to figure out this, have looked in many places but because of my table structure I'm struggling. Here is my table structure:

users - id, login (pk)

lessons - 开发者_JAVA技巧id (pk), name

users_to_lessons - users_LOGIN (fk), lessons_ID (fk)

How can I implement a "customers who bought this also bought this..." feature?

---

I don't really understand your question and what you need.
Anyway, assuming your user with id = my_user_id gets a lesson with id = my_lesson_id and you want to know all the lessons of other users who got my_lesson_id, try this:

SELECT DISTINCT l.id, l.name, COUNT(l.id) as rank
FROM lessons l JOIN users_to_lessons ul
ON l.id = ul.lessons_ID
WHERE l.id<>my_lesson_id AND
  ul.users_LOGIN IN
  (SELECT DISTINCT us.login FROM users us
   JOIN users_to_lessons ls ON us.login = ls.users_LOGIN
   WHERE ls.lessons_ID = my_lesson_id
      AND us.id<>my_user_id)
GROUP BY l.id
ORDER BY rank DESC, l.name    

---

Try one of these:

select 
    lessons_ID 
from 
    users_to_lessons
where 
    user_LOGIN IN
    (
        select 
            user_LOGIN 
        from 
            users_to_lessons
        where   
            lessons_ID = 1
    )
AND 
    lessons_ID <>1
;

select 
    ul2.lessons_ID
from 
    users_to_lessons ul1
inner join 
    users_to_lessons ul2
on 
    ul1.user_LOGIN = ul2.user_LOGIN
where 
    ul1.lessons_ID = 1
AND 
    ul2.lessons_ID <> 1
;