Multiple joins on same table

I’m trying to achieve a query which seems simple but I can’t make it work correctly. Here’s my database tables structures:

members
    -> id
    -> last_name
    -> first_name

activities
    -> id

registrations
    -> id
    -> member_id

tandems
    -> id
    -> activitie_id
    -> registration_member_one
    -> registration_member_two

Here’s what i want to achieve:

Mutliple members can register to an activity. Then, i group the registrations by tandems. I want a view with all the tandems listed and there’s my problem. When I try a query, it gives me multiple rows, duplicated many times.

Below, an example of the table I want to have:

tandems.id | activities.id | registration_member_one.members.last_name | registration_member_two.members.last_name
1          | 3             | John Doe                                  | Jane Doe

Here’s the query I’m working on:

SELECT
tandems.*,
memberOne.id, memberOne.last_name, memberOne.first_name,
memberTwo.id, memberTwo.last_name, memberTwo.first_name,
memberOne_registration.member_id as memberOne,
memberTwo_registration.member_id as memberTwo

FROM tandems

JOIN registrations as memberOne_registration
ON memberOne_registration.member_id = tandems.registration_member_one
JOIN members as memberOne ON memberOne.id = memberOne_registration.member_id

JOIN registrations as memberTwo_registration
ON memberTwo_registration.member_id = tandems.registration_member_two
JOIN members as memberTwo ON memberTwo.id = memberTwo_registration.member_id

WHERE activitie_id = 3;

Any help appreciated!

Answer

The error is caused by joining wrong column (member_id) of registrations table with tandems table, instead column registrations.id should be used.

SELECT
tandems.*,
memberOne.id, memberOne.last_name, memberOne.first_name,
memberTwo.id, memberTwo.last_name, memberTwo.first_name,
memberOne_registration.id as memberOne,
memberTwo_registration.id as memberTwo

FROM tandems
JOIN registrations as memberOne_registration ON memberOne_registration.id = tandems.registration_member_one
JOIN members as memberOne ON memberOne.id = memberOne_registration.member_id
JOIN registrations as memberTwo_registration ON memberTwo_registration.id = tandems.registration_member_two
JOIN members as memberTwo ON memberTwo.id = memberTwo_registration.member_id

WHERE activitie_id = 3;