Join two tables without repeating the primary table's rows

Hey guys i have two tables like so

//trainings table
|id|training_name|
|1 | training one   |

and the participants table

|id|training_id|participant_info|
|1 |1|info|
|2 |1|info|
|3 |1|info|
|4 |1|info|

When i join these tables using

DB::table('trainings')->join('training_participant_info', 'trainings.id', '=', 'training_participant_info.training_id')->get();

I get 4 records in total but i want to use something like distinct on the participants table before joining them together so ill get one for each training. It doesn't matter which one of the participant table records will be left i just want a single record joining for every training. Note i am gonna do extra queries after that so i don't want to use distinct afterwards i just want to join both of them with single participant info.

alanholmes

6 years ago

Best Answer

Level 35

@falloutboi

If you are not bothered by which row of the participants table you get, then you could just use Group By

DB::table('trainings')->join('training_participant_info', 'trainings.id', '=', 'training_participant_info.training_id')->groupBy('trainings.id')->get();

https://laravel.com/docs/6.x/queries#ordering-grouping-limit-and-offset

Please or to participate in this conversation.