Help with SQL query - how to retrieve year & company
Posted: Wed Jul 20, 2016 6:10 pm
Dear community,
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.
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' )
Thanks a lot in advance.
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.
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' )
Thanks a lot in advance.