SQL Multiple joins on same table

Hi!

It's not quiet an Eloquent question. The team I'm working with needs an traditional sql query..

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. A tandem is composed by two registrations that are linked to the members table. 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 tandems.activitie_id = 3;

Any help appreciated!

Tray2

6 years ago

Level 73

I had a similar issue in my project and this is how I solved it using the query builder

DB::table('books')
  ->join('genres', 'books.genre_id', '=', 'genres.id')
  ->join('formats', 'books.format_id', '=', 'formats.id')
  ->join('author_books', 'author_books.book_id', '=','books.id') 
  ->join('authors', 'authors.id', '=','author_books.author_id')
  ->orderBy('authors.last_name')
  ->orderBy('authors.first_name')
  ->get();

In plain SQL it would look something loke this

"select * from `books` inner join `genres` on `books`.`genre_id` = `genres`.`id` inner join `formats` on `books`.`format_id` = `formats`.`id` inner join `author_books` on `author_books`.`book_id` = `books`.`id` inner join `authors` on `authors`.`id` = `author_books`.`author_id` order by `authors`.`last_name` asc, `authors`.`first_name` asc"

Please or to participate in this conversation.