How to retrieve patents with at least one author to a certain country

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

Maria Tsouri
Posts: 2
Joined: Wed Aug 31, 2022 1:56 pm

How to retrieve patents with at least one author to a certain country

Post by Maria Tsouri » Wed Aug 31, 2022 2:52 pm

Hi to all! I am sure that there is somewhere this subject already solved... but I cannot find it searching
Anyway, I am trying to retrieve patents with at least one inventor (or applicant) located in Norway. I get back the results I want, but if the patent has inventors that are not located in Norway (co-inventors) then those inventors do not appear in the list. So I suppose I have somewhere a mistake (or a misunderstanding) at the syntax of my query. The code is the following:

Code: Select all

SELECT DISTINCT t1.appln_id AS ApplicationID1, t1.appln_auth AS ApplicationAuthority, t1.appln_nr AS ApplicationNumber, t1.nb_applicants AS NumberOfApplicants, t1.nb_inventors AS NumberOfInventors, t1.earliest_publn_year AS EarliestPublicationYear, t1.granted AS IsItGranted,
t2.cpc_class_symbol AS CPCClassSymbol,
t3.appln_title AS ApplicationTitle,
t4.pat_publn_id AS PublicationID, t4.publn_auth AS PublicationAuthority, t4.publn_nr AS PublicationNumber, t4.publn_kind AS PublicationKind, t4.publn_date AS PublicationDate,
t5.person_id AS PersonID, t5.applt_seq_nr AS PlaceListApplicants, t5.invt_seq_nr AS PlaceListInventors,
t6.person_name AS PersonName, t6.person_address AS PersonAddress, t6.person_ctry_code AS PersonCountryCode, t6.nuts AS NUTSRegion, t6.nuts_level AS NUTSLevel, t6.psn_name AS PersonPATSTATName, t6.psn_id AS PersonPATSTATID, t6.psn_sector AS PersonSector,
t9.last_name AS PersonLastName, t9.first_name AS PersonFirstName, t9.address_freeform AS AddressFreeform, t9.city AS PersonCity, t9.zip_code AS PersonZIPCode, t9.state AS PersonState, t9.person_ctry_code AS PersonCountryCode
FROM tls201_appln t1
FULL JOIN tls224_appln_cpc t2 ON t1.appln_id = t2.appln_id
FULL JOIN tls202_appln_title t3 ON t1.appln_id=t3.appln_id
FULL JOIN tls211_pat_publn t4 ON t1.appln_id = t4.appln_id
FULL JOIN tls207_pers_appln t5 ON t1.appln_id = t5.appln_id
FULL JOIN tls206_person t6 ON t5.person_id = t6.person_id
FULL JOIN tls226_person_orig t9 ON t5.person_id = t9.person_id
WHERE t6.person_ctry_code = 'ΝΟ'
AND
t4.publn_date BETWEEN '2008-01-01' AND '2008-12-31'
AND t1.granted = 'Y'
ORDER BY t1.appln_id;
I have the impression that I should nest the entire code in the person_ctry_code...

Thanks in advance!
Best,
Maria


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

Re: How to retrieve patents with at least one author to a certain country

Post by EPO / PATSTAT Support » Thu Sep 01, 2022 8:43 pm

Hello Maria,
your thinking is correct, the "Norwegian inventor or applicant" condition should be achieved via a subquery in your WHERE clause. Writing "t6.person_ctry_code = 'ΝO' " will limit your result list to only Norwegian persons. Which is correct as such, but not what you wanted. Below is a small correction. You should also use JOIN and not FULL JOIN.
Observe also that you will get a new row for each variation of the data of all the tables you have joined.
In your case, you joined the CPC table. Therefore an application with 1 inventor/applicant having 7 CPC codes will show 7 rows. The same with the publication table; you will get all the data replicated for each publication instance on a single application. For example, if the application with 1 Norwegian inventor/applicant and 7 CPC codes is a European patent then the EP A1 and EP B1 publications will lead to a total of 14 rows all generated by 1 single application. (and so on if even further publications are in the tls211 table)
That might be useful if you intend to further aggregate the data, but if not you can remove the tls211 table from the joins. Depends a bit on what other data you need from the tls211 and tls224 tables ...

Code: Select all

SELECT distinct  t1.appln_id AS ApplicationID1, t1.appln_auth AS ApplicationAuthority, t1.appln_nr AS ApplicationNumber, 
t1.nb_applicants AS NumberOfApplicants, t1.nb_inventors AS NumberOfInventors, t1.earliest_publn_year AS EarliestPublicationYear,
t1.granted AS IsItGranted,
t2.cpc_class_symbol AS CPCClassSymbol,
t3.appln_title AS ApplicationTitle,
t4.pat_publn_id AS PublicationID, t4.publn_auth AS PublicationAuthority, t4.publn_nr AS PublicationNumber, t4.publn_kind AS PublicationKind, t4.publn_date AS PublicationDate,
t5.person_id AS PersonID, t5.applt_seq_nr AS PlaceListApplicants, t5.invt_seq_nr AS PlaceListInventors,
t6.person_name AS PersonName, t6.person_address AS PersonAddress, t6.person_ctry_code AS PersonCountryCode, t6.nuts AS NUTSRegion, t6.nuts_level AS NUTSLevel, t6.psn_name AS PersonPATSTATName, t6.psn_id AS PersonPATSTATID, t6.psn_sector AS PersonSector,
t9.last_name AS PersonLastName, t9.first_name AS PersonFirstName, t9.address_freeform AS AddressFreeform, t9.city AS PersonCity, t9.zip_code AS PersonZIPCode, t9.state AS PersonState, t9.person_ctry_code AS PersonCountryCode
FROM tls201_appln t1
 JOIN tls224_appln_cpc t2 ON t1.appln_id = t2.appln_id
 JOIN tls202_appln_title t3 ON t1.appln_id=t3.appln_id
 JOIN tls211_pat_publn t4 ON t1.appln_id = t4.appln_id
 JOIN tls207_pers_appln t5 ON t1.appln_id = t5.appln_id
 JOIN tls206_person t6 ON t5.person_id = t6.person_id
 JOIN tls226_person_orig t9 ON t5.person_id = t9.person_id
WHERE --t6.person_ctry_code = 'NO' this line is commented out
t1.appln_id in (select appln_id from tls207_pers_appln join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id where person_ctry_code = 'NO')
AND t4.publn_date BETWEEN '2008-01-01' AND '2008-12-31'
AND t1.granted = 'Y'
ORDER BY t1.appln_id
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Maria Tsouri
Posts: 2
Joined: Wed Aug 31, 2022 1:56 pm

Re: How to retrieve patents with at least one author to a certain country

Post by Maria Tsouri » Fri Sep 02, 2022 10:33 am

Thank you very very much!
Yes when I combine CPC with inventors the query takes quite longer, as the inventors and the CPCs are repeated. In this, there can be solutions either get the entire table and clean it (as in my case I need both CPCs and inventors) or download two different tables and combine them afterwards. I don't know which is faster, but I have a feeling that it will be the second choice.

Thanks a lot again for the fast and effective answer!
Maria


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

Re: How to retrieve patents with at least one author to a certain country

Post by EPO / PATSTAT Support » Wed Sep 28, 2022 11:23 am

Hello Maria,
It really depends on what data you need. You say you would "clean it", but all the data is in fact "correct", so it does not need to be cleaned. But maybe you don't need all the data, or you want it in a different form/layout ? You can compare the output of an SQL query with what you would find in an excel sheet, where each row is a unique record and each column is a unique attribute.
If you want to have 1 "cell" containing more then 1 data item then you will need to first make a table (via a subroutine) that creates that combined data item. (via for example STRING_AGG functionality)
Below is an example that gives combined inventors and CPC codes of a limited data set based on a selection of patents that have been assigned a CPC tag starting with 'Y02T 10'.
You can adapt it to your needs.

Code: Select all

SELECT DISTINCT a.appln_id,a.appln_auth ,a.appln_nr, a.earliest_filing_date, 
a.nb_applicants,a.nb_inventors, a.docdb_family_id, a.docdb_family_size, 
INVENTOR_GROUP,
CPC_GROUP
FROM tls201_appln a
left join (select pa.appln_id ,STRING_AGG (cast((psn_name+' ['+person_ctry_code+']'+' ['+psn_sector+']' )as NVARCHAR(MAX)), '¦ ') INVENTOR_GROUP
FROM  tls207_pers_appln pa join tls206_person pap on pa.person_id = pap.person_id where pa.invt_seq_nr > 0 group by pa.appln_id) inventors
on a.appln_id = inventors.appln_id
join (select appln_id ,STRING_AGG (cast((cpc_class_symbol)as NVARCHAR(MAX)), '¦ ') WITHIN GROUP (ORDER BY cpc_class_symbol asc) CPC_GROUP
FROM  tls224_appln_cpc group by appln_id) CPC
ON a.appln_id = CPC.appln_id
where (a.appln_id in (select appln_id from tls224_appln_cpc where left(cpc_class_symbol,8) = 'Y02T  10'))
ORDER BY a.appln_auth,a.earliest_filing_date
PATSTAT Support Team
EPO - Vienna
patstat @ epo.org


Post Reply