How to construct Eloquent query from this SQL statement

Try

App\Batting::whereId(72548)->with('player:id,firstName,lastName')->get();

See "Eager Loading Specific Columns": https://laravel.com/docs/5.6/eloquent-relationships#eager-loading

I have been down this road, and not having much success. I have tried the following, trying your version:

>>> App\Batting::whereId(72548)->with('player:id,players.firstName,players.lastName')->get();
Illuminate/Database/QueryException with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'players.firstName' in 'field list' (SQL: select `id`, `players`.`firstName`, `players`.`lastName` from `players` where `players`.`playerID` in (troutmi01))'
>>>

You have to have models (relations) setup correctly. Study again in docs.

OK, I have it sort of working...

App\Batting::whereId(72548)->with('player:playerID,players.nameFirst,players.nameLast')->first();

returns the correct columns, but the players table is a nested object of batting, need a flattened) object (and only need specific columns from batting table)

Currently

=> App\Batting {#2924
     id: 72548,
     playerID: "troutmi01",
     yearID: 2015,
     stint: 1,
....
     player: App\Player {#2978
       playerID: "troutmi01",
       nameFirst: "Mike",
       nameLast: "Trout",
     },
   }

AFAIK, the relationship is correct.

use App\Player;

class Batting extends Model
{
    protected $guarded = ['id'];

    public function player()
    {
        return $this->hasOne(Player::class, 'playerID', 'playerID');
    }
}

Oops lol. It looks like I wrote the wrong column names (as you did lol).

If the query in your first post is correct and working, it should be nameFirst, nameLast. My brain just automatically converted them to firstName, lastName as that's what I typically use.

You shouldn't have to specify the players table when telling it what columns you're wanting. You're building up the query on the players table, so they're unnecessary.

but the players table is a nested object of batting

That's how relationships work.

and only need specific columns from batting table

So select those columns?

App\Batting::select('id, playerID, col1, col2')
    ->whereId(72548)
    ->with('player:playerID,nameFirst,nameLast')
    ->first();

So, yes the following does indeed return required data (getting closer) but I still need to flatten the result object

App\Batting::whereId(72548)->with('player:playerID,nameFirst,nameLast')->first();

I still need to flatten the result object

So you have a collection now after the query runs, so you can use those methods on the result. https://laravel.com/docs/5.6/eloquent-collections#available-methods

The collapse one might be the one you want.

Got it, that is just where I was headed (flatten, that only returns values, the collect method is indeed the final piece) in the docs :-)