I'm trying to create a complex query. My objective is to find patents that cite at least three patents from different technological fields. I have already created a search string which shows me the technological fields of the cited patents. I still need to check whether there are at least three cited patents from different technological fields. Any ideas? Following you will find my first query and two slides how the check should work.
Query:
Code: Select all
SELECT cited_publication.appln_id as cited_appln_id, cited_publication.publn_auth as cited_publn_auth
,cited_publication.publn_nr cited_publn_nr
,cited_publication.publn_kind cited_publn_kind
,cited_publication.publn_date cited_publn_date
,techn_field.techn_field_nr
,techn_field.weight
,citing_publn.publn_auth citing_publn_auth
,citing_publn.publn_nr citing_publn_nr
,citing_publn.publn_kind citing_publn_kind
,citing_publn.publn_date citing_publn_date
, citing_publn.appln_id citing_appln_id
FROM tls211_pat_publn cited_publication
JOIN tls212_citation on cited_publication.pat_publn_id = tls212_citation.cited_pat_publn_id
JOIN tls211_pat_publn citing_publn on tls212_citation.pat_publn_id = citing_publn.pat_publn_id
JOIN tls230_appln_techn_field techn_field on cited_publication.appln_id = techn_field.appln_id
Slides: