Help with SQL query - How to retrieve name of the patent's applicant(s)
Posted: Thu Sep 03, 2020 11:16 am
Hi everyone,
I am currently working with PATSTAT Spring 2020 edition. My aim is to retrieve all the name of the patent's applicant(s) for a limited number of UK biotech companies from 2016 to 2020.
Please find the codes I've written below. Here is a brief description of the methodology I used:
Use table tls201_appln to identify the appIn_id of the entries, appln_id and appIn_filing_date =YYYY-MM-DD
Use table tls209_appln_ipc to identify the ipc_class_symbol of the entries. I am only interested in patents with LIKE 'C12N%'
Use table tls202_aapln_title to identify the appln_title.
Join the three table person_id as a primary key.
My issue here is that I am not sure how to retrieve each patent's applicant(s) using SQL. I can match manually by checking the bibliographic data from Espacenet patent search (e.g., https://worldwide.espacenet.com/publica ... date=&FT=D). Is there any other way to retrieve the applicant's name using SQL?
On a side note, is it possible to merge PATSTAT with Orbis, to save time finding and matching each patent to a company's profile?
Any advice is much appreciated. Many thanks in advance.
I am currently working with PATSTAT Spring 2020 edition. My aim is to retrieve all the name of the patent's applicant(s) for a limited number of UK biotech companies from 2016 to 2020.
Please find the codes I've written below. Here is a brief description of the methodology I used:
Use table tls201_appln to identify the appIn_id of the entries, appln_id and appIn_filing_date =YYYY-MM-DD
Use table tls209_appln_ipc to identify the ipc_class_symbol of the entries. I am only interested in patents with LIKE 'C12N%'
Use table tls202_aapln_title to identify the appln_title.
Join the three table person_id as a primary key.
Code: Select all
SELECT t1.appln_id, t1.appln_auth, t1.appln_filing_date, t2.ipc_class_symbol, t3.appln_title
FROM
tls201_appln t1
JOIN
tls209_appln_ipc t2 ON t1.appln_id = t2.appln_id
JOIN
tls202_appln_title t3 ON t3.appln_id = t1.appln_id
WHERE
t2.ipc_class_symbol LIKE 'C12N%'
AND t1.appln_auth IN ('GB')
AND t1.appln_filing_date between '2016-01-01' and '2020-01-01'
On a side note, is it possible to merge PATSTAT with Orbis, to save time finding and matching each patent to a company's profile?
Any advice is much appreciated. Many thanks in advance.