For my research I need to create a (interfirm) patent collaboration network. I don't want to include inventors, but education or university collaborations with eachother and/or other firms are fine. Also I would like at least one of the applicants (/firms) to be based in the Netherlands.
I am wondering how to query this in sql without creating a table/dataset per amount of applicants and without exceeding Patstat Online's limitations.
Subsequently I am also curious how to eliminate or prevent the duplicate rows that result from my current method (where the p1 of the row above becomes p2).
My current code is this;
Code: Select all
select a.appln_id, tit.appln_title,
p1.doc_std_name as name1, p1.person_ctry_code as cc1, p1.person_address as addres1, free1.address_freeform,
p2.doc_std_name as name2, p2.person_ctry_code as cc2, p2.person_address as addres2, free2.address_freeform,
p3.doc_std_name as name3, p3.person_ctry_code as cc3, p3.person_address as addres3, free3.address_freeform,
p4.doc_std_name as name4, p4.person_ctry_code as cc4, p4.person_address as addres4, free4.address_freeform,
a.granted, a.ipr_type, cpc.cpc_class_symbol as cpc_class, ab.appln_abstract
from tls206_person p1
join tls207_pers_appln pa1 on p1.person_id = pa1.person_id
join tls207_pers_appln pa2 on pa1.appln_id = pa2.appln_id
join tls207_pers_appln pa3 on pa1.appln_id = pa3.appln_id
join tls207_pers_appln pa4 on pa1.appln_id = pa4.appln_id
join tls201_appln a on pa1.appln_id = a.appln_id
join tls202_appln_title tit on pa1.appln_id = tit.appln_id
join tls203_appln_abstr ab on pa1.appln_id = ab.appln_id
join tls224_appln_cpc cpc on pa1.appln_id = cpc.appln_id
join tls206_person p2 on pa2.person_id = p2.person_id
join tls206_person p3 on pa3.person_id = p3.person_id
join tls206_person p4 on pa4.person_id = p4.person_id
join tls226_person_orig free1 on p1.person_id = free1.person_id
join tls226_person_orig free2 on p2.person_id = free2.person_id
join tls226_person_orig free3 on p3.person_id = free3.person_id
join tls226_person_orig free4 on p4.person_id = free4.person_id
where (p1.person_ctry_code = 'NL' )
and (pa1.applt_seq_nr > 0)
and(pa2.applt_seq_nr > 0)
and(pa3.applt_seq_nr > 0)
and(pa4.applt_seq_nr > 0)
and (pa1.invt_seq_nr <1)
and(pa2.invt_seq_nr < 1)
and(pa3.invt_seq_nr < 1)
and(pa4.invt_seq_nr < 1)
and(pa1.person_id != pa2.person_id)
and(pa1.person_id != pa3.person_id)
and(pa2.person_id != pa3.person_id)
and(pa4.person_id != pa1.person_id)
and(pa4.person_id != pa2.person_id)
and(pa4.person_id != pa3.person_id)
and (a.earliest_filing_year between 2008 and 2013)
and (a.nb_applicants=4)
group by p1.doc_std_name, p1.person_ctry_code,p1.person_address, p4.doc_std_name, p4.person_ctry_code, p4.person_address, free4.address_freeform, p2.doc_std_name, p2.person_ctry_code,p2.person_address, a.appln_id, a.granted, a.ipr_type, tit.appln_title, ab.appln_abstract, cpc.cpc_class_symbol, free1.address_freeform, free2.address_freeform, p3.doc_std_name, p3.person_ctry_code, p3.person_address, free3.address_freeformKind regards,
Valérie