I'm working on a PATSTAT Online (Spring 2016) patent query. My aim is to find all the entries that include the applications made by a certain company, in a certain year (filing date).
The only table where I could find entries related to the companies is the tls206_person > psn_name. I'm not sure if this is correct.
The code I've written can be found below. Here is a brief description of the methodology I used:
Use table tls201_appln to identify the appIn_id of the entries with appIn_filing_year =YYYY
Use table tls206_person to identify the person_id of the entries with psn_name= companyname
Join the two table using tls207_pers_appIn which is the only table in the database that connects an appIn_id to a person_id.
Code: Select all
SELECT tls201_appln.*, tls207_pers_appln.*, tls206_person.* FROM tls201_appln JOIN tls207_pers_appln ON tls207_pers_appln.appln_id = tls201_appln.appln_id JOIN tls206_person ON tls206_person.person_id = tls207_pers_appln.person_id WHERE ( tls206_person.psn_name LIKE '%NAMEOFCOMPANY%' ) AND ( tls201_appln.appln_filing_year LIKE 'YEAR' )