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

rfuchs
Posts: 1
Joined: Wed Jul 20, 2016 12:32 pm

Help with SQL query - how to retrieve year & company

Post by rfuchs » 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.


mkracker
Posts: 120
Joined: Wed Sep 04, 2013 6:17 am
Location: Vienna

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

Post by mkracker » Tue Jul 26, 2016 10:42 am

Yes, this looks good.

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


Post Reply