Help with SQL query - how to retrieve year & company!!

Here you can post your opinions, ask questions and share experiences on the PATSTAT product line. Please always indicate the PATSTAT edition (e.g. 2015 Autumn Edition) and the database (e.g. PATSTAT Online, MySQL, MS SQL Server, ...) you are using.
Post Reply

marvelsferb7
Posts: 2
Joined: Wed Apr 29, 2020 9:16 am

Help with SQL query - how to retrieve year & company!!

Post by marvelsferb7 » Tue May 05, 2020 2:11 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.

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' )
Thanks a lot in advance.


EPO / PATSTAT Support
Posts: 425
Joined: Thu Feb 22, 2007 5:33 pm
Contact:

Re: Help with SQL query - how to retrieve year & company!!

Post by EPO / PATSTAT Support » Wed May 13, 2020 4:32 pm

Your query is correct.
Here is a small adaptation that will explicitly look for applicants and exclude inventors.

Code: Select all

SELECT psn_name, person_ctry_code , applt_seq_nr , tls201_appln.*
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 '%BOSCH%'  
AND applt_seq_nr > 0 and invt_seq_nr = 0
AND ( tls201_appln.appln_filing_year between 2000 and 2005)
order by appln_filing_date
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply