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.
Help with SQL query - how to retrieve year & company
Re: Help with SQL query - how to retrieve year & company
Yes, this looks good.
Nevertheless, here a slightly adapted version:
Nevertheless, here a slightly adapted version:
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 '%SONY%'
AND tls201_appln.appln_filing_year = 2000
AND tls207_pers_appln.applt_seq_nr > 0
-- retrieve applicants only, and not persons which are only inventors
- "APPLICATION_FILING_YEAR LIKE '2000':
- this attribute is defined as number, so the value 2000 needs not be enclosed in quotes
- for this comparison you do not need wild cards, so you should use the =-operator instead LIKE - I added APPLT_SEQ_NR > 0 to exclude inventors-only.
Try to run the query with APPLT_SEQ_NR = 0 and you will see what you would retrieve otherwise. - I am sure you know that there are multiple name attributes available in TLS206_PERSON. PSN_NAME is a good choice, but you could also experiment with DOC_STD_NAME or HAN_NAME instead.
-------------------------------------------
Martin Kracker / EPO
Martin Kracker / EPO