Confused about inner joins and self joins in the same query

1

All:
I am trying to retrieve Lifetime batting stats from a baseball database... for each player, I want total homeruns and total atbats (from the batting table), as well as first and last names from another table (people table). How to accomplish this using SQL? (Please give examples). How can you incorporate a self join with an inner join in the same query?

Batting Table People Table
playerID playerID
atBats firstname
homeruns lastname

I’m a newbie, and quite frustrated with all the things I’ve tried, with no (or incorrect) results.
(Batting table only has single season records)… playerid in BOTH tables
Thanks in advance

Perplexed in Pittsburgh

2

If you provide DDL and some sample data along with expected output, we can help. It doesn't need to be huge. Just simple Create table.... insert into table...

3 
SELECT
    PT.firstname, PT.lastname, BT.atBats, Bt.homeruns
FROM (
    SELECT playerID, SUM(atBats) AS atBats, SUM(homeruns) AS homeruns
    FROM [Batting Table]
    GROUP BY playerID
) AS BT
INNER JOIN [People Table] PT ON PT.playerID = BT.playerID
4

using our own sample data

image

use sqlteam
go

create table #people(playerID int, firstname nvarchar(50), 
lastname nvarchar(50))
insert into #people
select 1, 'Luke', 'Skywalker' union
select 2, 'Darth', 'Vader'

create table #batting(playerID int, season int, atBats int, homeruns int)
insert into #batting
select 1, 2018, 12, 2 union
select 1, 2019, 23, 12 union
select 1, 2019, 46, 33 union
select 2, 2018, 3, 0

select p.firstname, p.lastname, b.atBats, b.homeruns
 from #people p
 join (select sum(atBats) atBats, sum(homeruns) homeruns, playerID  
         from #batting 
		 group by playerID
		 ) b on p.playerID = b.playerID

--Another way
select p.firstname, p.lastname, sum(atBats) atBats, sum(homeruns)
 from #people p
 join #batting b on p.playerID = b.playerID
 group by p.firstname, p.lastname

 drop table #people
 drop table #batting