Merging Pitchf/x tables Correctly

Question

I need all the pitches and who threw them. I have downloaded the data into a sqlite db using the PitchRx library in R. All the pitch data is stored in a table called "pitch" and the pitcher name and ID number is stored in "atbat". The way I saw this done on this site uses the SQL code:

 SELECT * FROM atbat INNER JOIN pitch ON
    (atbat.num = pitch.num AND atbat.url = pitch.url)
    WHERE atbat.pitcher_name = 'Mariano Rivera'

But there are duplicates of pairs in the "pitch" table with "num" and "url", so you could be merging Mariano Rivera's name with a pitch from a different pitcher who threw the same day. I don't believe this joining the tables correctly. What is the correct way to join this data?

Note: I am using the SQL code in the sqlite3 library in python so there may be issues with the SQL code.

Answer 1

The following SQL statement works fine to merge the tables.

SELECT * FROM atbat INNER JOIN pitch ON
(atbat.num = pitch.num AND atbat.url = pitch.url)

Answer 2

While your own solution to this dilemma is correct, it might help someone else to explain why your posted solution works, especially if they used another tutorial using another approach to scrape MLB game data, one that doesn't give you the URL of the webpage identifying the location from where game-specific data was scraped.

Initially, I struggled with the same question. Namely, how can we be certain that the correct pitch will be correctly merged with its corresponding at-bat upon merging the "pitch" table with the "at_bat" table?

I realized that the at-bat numbers for each game (column num) start numbering from the first pitch of the game and are sequentially numbered through to the end of the game; The numbering doesn't stop at the end of each pitcher's appearance and start again with the onset of a new pitcher's appearance as I (and I'm guessing you) originally thought.

So, given that, one can merge the two tables using an inner join on a composite primary key consisting of num and url. So, to uniquely identify each row, we need the appropriate game-specific information including game-id, game date, away team, home team (which are included in and indicated by the url) and the at-bat number (which is reflected by num) such that each pitcher's unique pitch (pitch_num) will fit within each unique at-bat (num) within each game.