I am using the PATSTAT Online database 2019 Autumn edition. I want to extract patent data by linking the 4 tables - 201, 224, 207 and 206. I want to see country-wise patents related to wind energy filed for the time period 1999 - 2018 tagged using the Y02E scheme. I want information related appln_id, earliest_filing_date, person_country_code, apple_auth.
Code: Select all
SELECT DISTINCT a.appln_id, a.appln_auth, a.appln_nr, a.appln_kind, a.earliest_filing_date, b.cpc_class_symbol, c.invt_seq_nr, d.person_name, d.person_ctry_code
FROM tls201_appln a JOIN tls224_appln_cpc b ON a.appln_id = b.appln_id
LEFT OUTER JOIN tls207_pers_appln c on a.appln_id = c.appln_id
LEFT OUTER JOIN tls206_person d ON c.person_id = d.person_id
WHERE b.cpc_class_symbol = 'Y02E 10/7%' AND c.invt_seq_nr > 0
ORDER BY a.appln_auth, a.earliest_filing_date, a.appln_id, c.invt_seq_nr, d.person_ctry_code
Thank you.