EPO - Discussion forums

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.

Yes, this looks good.

Nevertheless, here a slightly adapted version:

• "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.