It should be something like this (haven’t tested):
$leagues = DB::table('leagues')
->select('league_name')
->join('countries', 'countries.country_id', '=', 'leagues.country_id')
->where('countries.country_name', $country)
->get();
$leagues will be instance of the Illuminate\Support\Collection object, so you can iterate over it using foreach for example.
You can pass 5th argument to the join() function which will specify type of join (default is “inner”).
If you’re using Eloquent and have “League” model then you can use join on the model too:
$leagues = League::select('league_name')
->join('countries', 'countries.country_id', '=', 'leagues.country_id')
->where('countries.country_name', $country)
->get();
In this case, $leagues will be an instance of Illuminate\Database\Eloquent\Collection which extends regular Laravel collections and gives you a bit more functionality than regular collections.
However, there is even an easier way to write this without using joins:
$leagues = League::select('league_name')->whereHas('countries', function($query) use ($country) {
$query->where('country_name', $country);
})->get();
Note that in this example “countries” is not a table name but Eloquent relationship name, so you need to set up your relationships before using this approach.
Also, instead of using join, this example will use two queries, or a nested query, I’m not sure; but something like this: SELECT league_name FROM leagues WHERE country_id IN (SELECT id FROM countries WHERE country_name='$country')
