Skip to main content
1 posts
Role: moderator
Last seen: 04/16/2021 - 10:00
Joined: 10/17/2017 - 07:47
How to join the bulk download tables prefixed with 'raw' (which contain the unprocessed information that appeared in the patent xml data) to the corresponding disambiguated tables.

The table (link below) shows which columns to use to join each table to the others to which it is connected. For example, if I wanted to join the raw inventor table to the inventor table (which has disambiguated inventor names) , I would use the 'inventor_id' column in the raw inventor table and the 'id' column in the inventor table. To do this in MySQL, I could do "select * from rawinventor ri left join inventor i on".

A few important notes here include that the rawinventor and rawassignee tables both join to the rawlocation table using the 'id' column in the rawlocation table not the 'location_id' column. Also, the rawexaminer and rawlawyer tables do not join to the rawlocation table at all, because this data is not available. There is no disambiguated examiner table corresponding to the rawexaminer table, so the rawexaminer table only joins to the patent table. If you want to join the rawinventor or rawassignee to the location table, you will need to first join to the rawlocation table and then use that to join to the location table.