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.