I am a PhD Student in Economics, and I need some clarifications on some data I am downloading. I need to download all the French, Italian and German patents involving a university. Not collaborative patents but ALL patents.
I, therefore, used the following code:
Code: Select all
SELECT distinct tls201_appln.appln_id, appln_auth,appln_nr, appln_filing_year, nb_applicants, nb_inventors, psn_name, tls206_person.NUTS, nuts_label , person_ctry_code, psn_sector, granted ,techn_field.techn_sector,techn_field.techn_field
FROM tls201_appln
join tls207_pers_appln on tls201_appln.appln_id = tls207_pers_appln.appln_id
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
join tls230_appln_techn_field on tls201_appln.appln_id = tls230_appln_techn_field.appln_id
join (SELECT distinct techn_field_nr, techn_sector, techn_field FROM tls901_techn_field_ipc) techn_field
on tls230_appln_techn_field.techn_field_nr = techn_field.techn_field_nr
join tls904_nuts on tls206_person.nuts = tls904_nuts.nuts
WHERE nb_applicants > 1
and
tls201_appln.appln_id in
(select appln_id from tls207_pers_appln join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id where psn_sector in
('UNIVERSITY'))
and applt_seq_nr > 0
and appln_filing_year between 1977 and 2018
and appln_auth = 'EP'
and tls201_appln.appln_id in (select appln_id from tls207_pers_appln
join tls206_person on tls207_pers_appln.person_id = tls206_person.person_id
where applt_seq_nr > 0 and person_ctry_code ='DE')
and techn_sector = 'Chemistry'
order by appln_filing_year, tls201_appln.appln_id
Best,
I.C.