Patent family landscape
Posted: Tue Mar 16, 2021 3:36 pm
Hello,
I want to find all of the pharmaceutical patent applications (technical field 16) filed between 2005 and 2015 that belong to DOC families that have at least one twin application in each of these seven offices: in seven offices: Argentina, Brazil, EPO, India, Japan, Korea and the US.
I tried to do it with the following code. However, the results include several families that do not have at least one twin in each office. How could I fix that problem?
All the best,
Eduardo Mercadante
PhD Student (LSE)
I want to find all of the pharmaceutical patent applications (technical field 16) filed between 2005 and 2015 that belong to DOC families that have at least one twin application in each of these seven offices: in seven offices: Argentina, Brazil, EPO, India, Japan, Korea and the US.
I tried to do it with the following code. However, the results include several families that do not have at least one twin in each office. How could I fix that problem?
Code: Select all
SELECT DISTINCT a.appln_id, a.appln_auth, a.appln_filing_year, a.granted, a.docdb_family_id, a.docdb_family_size
FROM tls201_appln a
JOIN tls230_appln_techn_field j ON a.appln_id = j.appln_id
WHERE a.docdb_family_id IN
(SELECT a.docdb_family_id
FROM tls201_appln a
JOIN tls230_appln_techn_field j ON a.appln_id = j.appln_id
WHERE j.techn_field_nr = 16
AND a.appln_auth = 'US')
AND a.docdb_family_id IN
(SELECT a.docdb_family_id
FROM tls201_appln a
JOIN tls230_appln_techn_field j ON a.appln_id = j.appln_id
WHERE j.techn_field_nr = 16
AND a.appln_auth = 'EP')
AND a.docdb_family_id IN
(SELECT a.docdb_family_id
FROM tls201_appln a
JOIN tls230_appln_techn_field j ON a.appln_id = j.appln_id
WHERE j.techn_field_nr = 16
AND a.appln_auth = 'JP')
AND a.docdb_family_id IN
(SELECT a.docdb_family_id
FROM tls201_appln a
JOIN tls230_appln_techn_field j ON a.appln_id = j.appln_id
WHERE j.techn_field_nr = 16
AND a.appln_auth = 'KR')
AND a.docdb_family_id IN
(SELECT a.docdb_family_id
FROM tls201_appln a
JOIN tls230_appln_techn_field j ON a.appln_id = j.appln_id
WHERE j.techn_field_nr = 16
AND a.appln_auth = 'BR')
AND a.docdb_family_id IN
(SELECT a.docdb_family_id
FROM tls201_appln a
JOIN tls230_appln_techn_field j ON a.appln_id = j.appln_id
WHERE j.techn_field_nr = 16
AND a.appln_auth = 'AR')
AND a.docdb_family_id IN
(SELECT a.docdb_family_id
FROM tls201_appln a
JOIN tls230_appln_techn_field j ON a.appln_id = j.appln_id
WHERE j.techn_field_nr = 16
AND a.appln_auth = 'IN')
AND j.techn_field_nr = 16
AND a.appln_filing_year BETWEEN 2000 AND 2015
-- added line below to illustrate the data resstriction issue.
order by docdb_family_id
Eduardo Mercadante
PhD Student (LSE)